Dealing with date values is a core part of working with T-SQL, and SQL Server has several built-in functions to access and manipulate dates in your T-SQL scripts, functions, and stored procedures. Below are some essential T-SQL functions that work with SQL Server’s classic datetime data type. SQL Server 2008’s newer functions also deal with the new date, time, and datetime2 data types.
1. GETDATE ()
Probably the most essential of the date4 functions,
SELECT GETDATE()
returns a datetime data type containing the current system data and time: 2009-07-07 11:52:26.687.
2. DATEADD (datepart, number, date)
DATEADD lets you add values to a given date and returns the result as a datetime data type. Entering
SELECT DATEADD(DAY, 30, GETDATE())
adds 30 days to the date from the example above: 2009-08-06 12:01:38.950.
3. DATEDIFF (datepart, startdate, enddate)
This function returns a single integer data type that represents the difference between two dates. It can return values for years, months, days, hours, minutes, seconds, milliseconds, and more:
SELECT DATEDIFF(DAY, '01/01/2009', GETDATE())
returns 187 as the difference in days between the example date and the beginning of the year.
4. DATEPART (datepart, date)
To return an integer that represents a portion of a valid date, DATEPART extracts all parts of the datetime data type including years, months, days, hours, minutes, seconds and milliseconds:
SELECT DATEPART(MONTH, GETDATE())
returns 7 as the example date’s month.
5. DATENAME (datepart, date)
Like its name suggests, DATENAME returns the name of a given part of the date:
SELECT DATENAME(MONTH, GETDATE())
It can return almost all parts of the date including the name of the quarter, the weekday, or as here, the month: July.
6. ISDATE (expression)
This function tests if the value supplied is a valid date:
SELECT ISDATE ('07/44/09')
In this case, it returns a value of 0 (false) indicating the date is invalid; if it returns a value of 1 (true), the date is valid.
7. DAY(date), MONTH(date), YEAR(date)
These date functions are like DATEPART but a bit easier to work with:
SELECT MONTH(0), DAY(0), YEAR(0)
They each return an integer representing the supplied date value—in this case, 1,1,1900.