Date Functions
There are many built-in functions available in SQL Server to work with date and time. Unlike aggregate functions, these functions can be used in WHERE clause as well. But for optimal performance, these functions should be avoided in the WHERE clause.
Following are the most common Date Time functions:
DATEPART: Returns an integer representing specified part for the specified date.
SYNTAX
DATEPART ( part , date )
EXAMPLE
Query:
SELECT DATEPART( MONTH , GETDATE() ) AS [DATEPART]
Output:
DATEPART
9
DATENAME: Returns name of the specified part for the specified date.
SYNTAX
DATENAME ( part , date )
EXAMPLE
Query:
SELECT DATENAME( MONTH , GETDATE() ) AS [DATENAME]
Output:
DATENAME
September
DATEADD: Adds or Subtracts specified interval of the specified part for the specified date.
SYNTAX
DATEADD ( part , interval , date )
EXAMPLE
Query:
SELECT DATEADD( DAY , 10 , GETDATE () ) AS [DATEADD]
Output:
DATEADD
10/2/2024 12:27:38 PM
DATEDIFF: Returns difference between start and end date for specified part.
SYNTAX
DATEDIFF ( part, start date, end date )
EXAMPLE
Query:
SELECT DATEDIFF( YEAR , '1/1/2000' , GETDATE () ) AS [DATEDIFF]
Output:
DATEDIFF
24