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



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.



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


SELECT DATEADD(datepart,nunber,date)

DATEDIFF()- Returns the time between two dates


SELECT DATEDIFF(datepart,startdate,enddate)

CONVERT() – Converts date in different date formats


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


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 = 15