T-SQL Classic Date Functions

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.

Related: T-SQL Function finds Almost Any Kind of Date

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.

Learn more: T-SQL Foundations: Thinking in Sets