Date Functions

Date functions manipulate datetime values. Date functions can be summarized as follows:

Syntax

date_ function (parameters)

where

date_ function

Specifies a date function.

The date functions are:

Date function Description

DATEADD
(datepart, number, date) Produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts.
If the date parameter is a smalldatetime value, the result is also a smalldatetime. You can use DATEADD to add seconds or milliseconds to a smalldatetime value, but the addition is meaningful only where the resulting date changes by at least 1 minute.

DATEDIFF
(datepart, date1, date2) Returns the number of datepart "boundaries" crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all datatypes such as minutes, seconds, and milliseconds.
DATEDIFF takes three parameters. The first is a datepart; the second and third are dates, either datetime or smalldatetime values. The result is a signed integer value equal to the number of datepart boundaries crossed between date2 minus date1. For example, the number of weeks between Sunday, January 4 and Sunday, January 11, is 1.

DATEDIFF produces an error if the result is out range for integer values. For milliseconds, the maximum number that can be represented is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number that can be represented is approximately 68 years.

If smalldatetime values are used, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are set to 0 for calculating the difference.

DATENAME
(datepart, date) Returns a character string representing the specified date part (datepart) of the specified date (date).

DATEPART
(datepart, date) Returns an integer representing the specified date part (datepart) of the specified date (date).

GETDATE() Returns the current system date and time in the SQL Server standard internal format for datetime values. GETDATE does not take any parameters.

date



Date function	Description
DATEADD (datepart, number, date)	Produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts. If the date parameter is a smalldatetime value, the result is also a smalldatetime. You can use DATEADD to add seconds or milliseconds to a smalldatetime value, but the addition is meaningful only where the resulting date changes by at least 1 minute.
DATEDIFF (datepart, date1, date2)	Returns the number of datepart "boundaries" crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all datatypes such as minutes, seconds, and milliseconds. DATEDIFF takes three parameters. The first is a datepart; the second and third are dates, either datetime or smalldatetime values. The result is a signed integer value equal to the number of datepart boundaries crossed between date2 minus date1. For example, the number of weeks between Sunday, January 4 and Sunday, January 11, is 1. DATEDIFF produces an error if the result is out range for integer values. For milliseconds, the maximum number that can be represented is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number that can be represented is approximately 68 years. If smalldatetime values are used, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are set to 0 for calculating the difference.
DATENAME (datepart, date)	Returns a character string representing the specified date part (datepart) of the specified date (date).
DATEPART (datepart, date)	Returns an integer representing the specified date part (datepart) of the specified date (date).
GETDATE()	Returns the current system date and time in the SQL Server standard internal format for datetime values. GETDATE does not take any parameters.

Can be either the GETDATE function, a character string in date format, or the name of a datetime column.

datepart

Is a parameter used with DATEADD, DATEDIFF, DATENAME, and DATEPART. The following table lists the date parts, the abbreviations recognized by SQL Server, and the acceptable values:



Date part	Abbreviation	Values
year	yy	1753-9999
quarter	qq	1-4
month	mm	1-12
day of year	dy	1-366
day	dd	1-31
week	wk	1-53
weekday	dw	1-7 (Sun.-Sat.)
hour	hh	0-23
minute	mi	0-59
second	ss	0-59
millisecond	ms	0-999

If the year is given with two digits, <50 is the next century and >=50 is this century. So "25" is "2025," and "50" is "1950."

Milliseconds can be preceded by either a colon or a period. If preceded by a colon, the number means thousandths of a second. If preceded by a period, a single digit means tenths of a second, two digits mean hundredths of a second, and three digits mean thousandths of a second. For example, "12:30:20:1" means 20 and one-thousandth seconds past 12:30; "12:30:20.1" means 20 and one-tenth seconds past 12:30.

Use the datetime datatype for dates after January 1, 1753. When entering datetime values, always enclose them in quotation marks. Use char or varchar for earlier dates.

SQL Server automatically converts between character and datetime values when necessary ¾ for example, when you compare a character value with a datetime value.

The date part weekday or dw returns the day of the week (Sunday, Monday, and so on) when used with datename. It returns a corresponding number (Sunday = 1, Saturday = 7) when used with datepart. Using weekday or dw with DATEADD and DATEDIFF is not logical and produces spurious results. Use day or dd instead.

Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either datename or datepart seconds and milliseconds are always 0.

Remarks

Date functions can be used in the select list or in the WHERE clause of a query.

Use the datetime datatype only for dates after January 1, 1753. When entering datetime values, always enclose them in quotation marks. Store as character data for earlier dates. SQL Server recognizes a wide variety of date styles. For more information about date and time data, see the Datatypes topic and CONVERT Function.

Examples

A. DATEADD Function

This example prints a listing of a "timeframe." This timeframe represents the existing publication date plus 21 days.

SELECT timeframe = DATEADD(day, 21, pubdate)
    FROM titles

B. DATEDIFF Function

This example determines the difference in days between the current date and the publication date.

SELECT newdate = DATEDIFF(day, pubdate, getdate())
    FROM titles

C. DATEDIFF Function Changes in SQL Server 6.0

This example shows the difference in results of the new DATEDIFF function in SQL Server 6.0.

In earlier releases:

SELECT "Number of Minutes" = DATEDIFF(minute, 'jan 1 1995 22:30:16', 'jan 1 1995 23:30:15')
go
Number of Minutes
------------------------
59

In SQL Server 6.0

SELECT "Number of Minutes" = DATEDIFF(minute, 'jan 1 1995 22:30:16', 'jan 1 1995 23:30:15')
go
Number of Minutes
------------------------
60

The difference is minor; however, it accurately reflects that 60 "minute boundaries" were crossed.

D. DATENAME and DATEPART Functions

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE( ) as well as some other date functions.

SELECT "Current Date" = getdate()
go
Current Date                
--------------------------- 
Feb 18 1995 11:46PM         

SELECT "Month Name" = DATENAME(month, getdate())
go
Month Name                     
------------------------------ 
February                       

SELECT "Month Number" = DATEPART(month, getdate())
go
Month Number 
------------ 
2