SQL Server Date Functions

While working with SQL Server Date function and to keep it simple, make sure that date column does not have time portion. Whenever you insert date in table verify that datatype of column should match with date format.

SQL Server Functions helps you to convert date and time values to other date and time formats.

There are some most important built-in date functions in SQL Server

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.