SQL Server date function with example

I enjoy writing T-SQL scripts, function and stored procedure for business but while working with SQL server date function it gets tricky, just keep it simple. Make sure that date column does not have time included. If it is include remove time from date using convert function. Whenever you insert date in table verify that datatype of column should match with date format. Different date function are available in SQL server to convert date in specific format

So while writing script when you face problem try SQL Server date functions. These function will helps you to convert date and time values to other date and time formats.

SQL Server Date Functions

Date function is use to extract some part of date while writing script. GETDATE() is popular function to provide current date, DATEDIFF, DATEPART and DATEADD are date function in SQL server

Lets understand these built-in date functions with their syntax and example

GETDATE() – It returns the current date and time of system

Syntax

SELECT GETDATE()

DATEPART() – It will return single part of a date time like you can get year, date, hour, minute, month and so on from given date.

Syntax

SELECT DATEPART(month, GETDATE())

DATEADD() – Using this function, you add or subtract a specified time interval from a date

Syntax

SELECT DATEADD(datepart,nunber,date)

DATEDIFF()- Returns the time between two dates

Syntax

SELECT DATEDIFF(datepart,startdate,enddate)

CONVERT() – Converts date in different date formats

Syntax

SELECT CONVERT(datatype(length),experssion,format)

SQL Server has built-in datatype for storing date or date/time in the table.

DATE – Only date format YYYY-MM-DD
DATETIME – Date time format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME – Small date time format: YYYY-MM-DD HH:MI:SS
TIMESTAMP – Generates unique number

Example

Lets try an example to understand the working of date datatype while querying the table.

SELECT Name, address, empid FROM employee_details 
WHERE Joining_date = '2008-15-04'

Result is as expected only if datatype of column Joining_date is DATE, If it is datetime or some other datatype then
it returns no result.

 

Enjoy learning !!!!

Leave a Reply

  

  

  


7 + = 16