Following is the list of date functions in MS SQL Server.
GETDATE()
It will return the current date along with time.
Syntax
The syntax for the above function −
GETDATE()
Example
The following query will return the current date along with time in MS SQL Server.
Select getdate() as currentdatetime
DATEPART()
It will return the part of date or time.
Syntax
The syntax for the above function −
DATEPART(datepart, datecolumnname)
Example
Example 1 − The following query will return the part of the current date in MS SQL Server.
Select datepart(day, getdate()) as currentdate
Example 2 − The following query will return the part of the current month in MS SQL Server.
Select datepart(month, getdate()) as currentmonth
DATEADD()
It will display the date and time by add or subtract the date and time interval.
Syntax
The syntax for the above function −
DATEADD(datepart, number, datecolumnname)
Example
The following query will return the after 10 days date and time from the current date and time in MS SQL Server.
Select dateadd(day, 10, getdate()) as after10daysdatetimefromcurrentdatetime
DATEDIFF()
It will display the date and time between two dates.
Syntax
The syntax for the above function −
DATEDIFF(datepart, startdate, enddate)
Example
The following query will return the difference of hours between 2015-11-16 and 2015-11-11 dates in MS SQL Server.
Select datediff(hour, 2015-11-16, 2015-11-11) as
differencehoursbetween20151116and20151111
CONVERT()
It will display the date and time in different formats.
Syntax
The syntax for the above function −
CONVERT(datatype, expression, style)
Example
The following queries will return the date and time in a different formats in MS SQL Server.
SELECT CONVERT(VARCHAR(19),GETDATE())
SELECT CONVERT(VARCHAR(10),GETDATE(),10)
SELECT CONVERT(VARCHAR(10),GETDATE(),110)
