date (Transact-SQL)

date (Transact-SQL)

SQL Server 2008 Books Online (May 2008)

date (Transact-SQL)

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

Defines a date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions see Using Date and Time Data.

date Description

Property	Value
Syntax	date
Usage	DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date )
Default string literal format (used for down-level client)	YYYY-MM-DD For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data.
Range	0001-01-01 through 9999-12-31 January 1, 1 A.D. through December 31, 9999 A.D.
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported String Literal Formats for date

The following tables show the valid string literal formats for the date data type.

Numeric	Description
mdy [m]m/dd/[yy]yy [m]m-dd-[yy]yy [m]m.dd.[yy]yy myd mm/[yy]yy/dd mm-[yy]yy/dd [m]m.[yy]yy.dd dmy dd/[m]m/[yy]yy dd-[m]m-[yy]yy dd.[m]m.[yy]yy dym dd/[yy]yy/[m]m dd-[yy]yy-[m]m dd.[yy]yy.[m]m ymd [yy]yy/[m]m/dd [yy]yy-[m]m-dd [yy]yy-[m]m-dd	[m]m, dd, and [yy]yy represents month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. Only four- or two-digit years are supported. Use four-digit years whenever possible. To specify an integer from 0001 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, use the two digit year cutoff Option. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. The default date format is determined by the current language setting. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements. The ydm format is not supported for date.

Numeric

Description

mdy

[m]m/dd/[yy]yy

[m]m-dd-[yy]yy

[m]m.dd.[yy]yy

myd

mm/[yy]yy/dd

mm-[yy]yy/dd

[m]m.[yy]yy.dd

dmy

dd/[m]m/[yy]yy

dd-[m]m-[yy]yy

dd.[m]m.[yy]yy

dym

dd/[yy]yy/[m]m

dd-[yy]yy-[m]m

dd.[yy]yy.[m]m

ymd

[yy]yy/[m]m/dd

[yy]yy-[m]m-dd

[m]m, dd, and [yy]yy represents month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators.

Only four- or two-digit years are supported. Use four-digit years whenever possible. To specify an integer from 0001 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, use the two digit year cutoff Option.

A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.

The default date format is determined by the current language setting. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements.

The ydm format is not supported for date.

Alphabetical	Description
mon [dd][,] yyyy mon dd[,] [yy]yy mon yyyy [dd] [dd] mon[,] yyyy dd mon[,][yy]yy dd [yy]yy mon [dd] yyyy mon yyyy mon [dd] yyyy [dd] mon	mon represents the full month name or the month abbreviation given in the current language. Commas are optional and capitalization is ignored. To avoid ambiguity, use four-digit years. If the day is missing, the first day of the month is supplied.

Alphabetical

Description

mon [dd][,] yyyy

mon dd[,] [yy]yy

mon yyyy [dd]

[dd] mon[,] yyyy

dd mon[,][yy]yy

dd [yy]yy mon

[dd] yyyy mon

yyyy mon [dd]

yyyy [dd] mon

mon represents the full month name or the month abbreviation given in the current language. Commas are optional and capitalization is ignored.

To avoid ambiguity, use four-digit years.

If the day is missing, the first day of the month is supplied.

ISO 8601	Descripton
YYYY-MM-DD YYYYMMDD	Same as the SQL standard. This is the only format that is defined as an international standard.

ISO 8601

Descripton

YYYY-MM-DD

YYYYMMDD

Same as the SQL standard. This is the only format that is defined as an international standard.

Unseparated	Description
[yy]yymmdd yyyy[mm][dd]	The date data can be specified with four, six, or eight digits. A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits. A four-digit string is interpreted as year.

Unseparated

Description

[yy]yymmdd

yyyy[mm][dd]

The date data can be specified with four, six, or eight digits. A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits. A four-digit string is interpreted as year.

ODBC	Description
{ d 'yyyy-mm-dd' }	ODBC API specific. Functions in SQL Server 2008 as in SQL Server 2005.

ODBC

Description

{ d 'yyyy-mm-dd' }

ODBC API specific.

Functions in SQL Server 2008 as in SQL Server 2005.

W3C XML format	Description
yyyy-mm-ddTZD	Specifically supported for XML/SOAP usage. TZD is the time zone designator (Z or +hh:mm or -hh:mm): hh:mm represents the time zone offset. hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset. MM is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset. + (plus) or – (minus) the mandatory sign of the time zone offset. This indicates that the time zone offset is added or subtracted from the Coordinated Universal Times (UTC) time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

W3C XML format

Description

yyyy-mm-ddTZD

Specifically supported for XML/SOAP usage.

TZD is the time zone designator (Z or +hh:mm or -hh:mm):

hh:mm represents the time zone offset. hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset.
MM is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset.
+ (plus) or – (minus) the mandatory sign of the time zone offset. This indicates that the time zone offset is added or subtracted from the Coordinated Universal Times (UTC) time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

ANSI and ISO 8601 Compliance

date complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–31 CE."

The default string literal format, which is used for down-level clients, complies with the SQL standard form which is defined as YYYY-MM-DD. This format is the same as the ISO 8601 definition for DATE.

Examples

The following example compares the results of casting a string to each date and time data type.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Here is the result set.

Data type	Output
time	12:35:29. 1234567
date	2007-05-08
smalldatetime	2007-05-08 12:35:00
datetime	2007-05-08 12:35:29.123
datetime2	2007-05-08 12:35:29. 1234567
datetimeoffset	2007-05-08 12:35:29.1234567 +12:15

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads

Reference

Other Resources

Help and Information