date (Transact-SQL)
Collapse the table of content
Expand the table of content

date (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Defines a date in SQL Server.

PropertyValue
Syntaxdate
UsageDECLARE @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 that follows.
Range0001-01-01 through 9999-12-31 (1582-10-15 through 9999-12-31 for Informatica)

January 1, 1 CE through December 31, 9999 CE (October 15, 1582 CE through December 31, 9999 CE for Informatica)
Element rangesYYYY is four digits from 0001 to 9999 that represent a year. For Informatica, YYYY is limited to the range 1582 to 9999.

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 length10 positions
Precision, scale10, 0
Storage size3 bytes, fixed
Storage structure1, 3-byte integer stores date.
AccuracyOne day
Default value1900-01-01

This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
CalendarGregorian
User-defined fractional second precisionNo
Time zone offset aware and preservationNo
Daylight saving awareNo

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

NumericDescription
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 Configure the two digit year cutoff Server Configuration Option.

 Note! For Informatica, YYYY is limited to the range 1582 to 9999.

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.
AlphabeticalDescription
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 8601Descripton
YYYY-MM-DD

YYYYMMDD
Same as the SQL standard. This is the only format that is defined as an international standard.
UnseparatedDescription
[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.
ODBCDescription
{ d 'yyyy-mm-dd' }ODBC API specific.
W3C XML formatDescription
yyyy-mm-ddTZDSpecifically 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.

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.

System_CAPS_ICON_note.jpg Note


For Informatica, the range is limited to 1582-10-15 (October 15, 1582 CE) to 9999-12-31 (December 31, 9999 CE).

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients.

SQL Server data typeDefault string literal format passed to down-level clientDown-level ODBCDown-level OLEDBDown-level JDBCDown-level SQLCLIENT
timehh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
dateYYYY-MM-DDSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]SQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmSQL_WVARCHAR or SQL_VARCHARDBTYPE_WSTRor DBTYPE_STRJava.sql.StringString or SqString

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

When the conversion is to time(n), the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time".

If the conversion is to datetime, the
date is copied and the time component is set to 00:00:00.000. The following code shows the results of converting a date value to a datetime value.

DECLARE @date date= '12-10-25';  
DECLARE @datetime datetime= @date;  
  
SELECT @date AS '@date', @datetime AS '@datetime';  
  
--Result  
--@date      @datetime  
------------ -----------------------  
--2025-12-10 2025-12-10 00:00:00.000  
--  
--(1 row(s) affected)  

In the case of conversion to smalldatetime, when the date value is in the range of a smalldatetime, the date component is copied and the time component is set to 00:00:00. When the date value is outside the range of a smalldatetime value, error message 242 is raised: "The conversion of a date data type to a smalldatetime data type results in an out-of-range value;and the smalldatetime value is set to NULL. The following code shows the results of converting a date value to a smalldatetime value.

DECLARE @date date= '1912-10-25';  
DECLARE @smalldatetime smalldatetime = @date;  
  
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@date      @smalldatetime  
------------ -----------------------  
--1912-10-25 1912-10-25 00:00:00  
--  
--(1 row(s) affected)  

When the conversion is to datetimeoffset(n), the date is copied, and the time is set to 00:00.0000000 +00:00. The following code shows the results of converting a date value to a datetimeoffset(3) value.

DECLARE @date date = '1912-10-25';  
DECLARE @datetimeoffset datetimeoffset(3) = @date;  
  
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@date      @datetimeoffset  
------------ ------------------------------  
--1912-10-25 1912-10-25 00:00:00.000 +00:00  
--  
--(1 row(s) affected)  

If the conversion is to datetime2(n), the date component is copied, and the time component is set to 00:00:00.00 regardless of the value of (n). The following code shows the results of converting a date value to a datetime2(3) value.

DECLARE @date date = '1912-10-25';  
DECLARE @datetime2 datetime2(3) = @date;  
  
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';  
  
--Result  
--@date      @datetime2(3)  
------------ -----------------------  
--1912-10-25 1912-10-25 00:00:00.00  
--  
--(1 row(s) affected)  

Converting date to Other Date and Time Types

This section describes what occurs when a date data type is converted to other date and time data types.

When the conversion is to time(n), the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time".

If the conversion is to datetime, date is copied. The following code shows the results of converting a date value to a datetime value.

DECLARE @date date= '12-10-25';  
DECLARE @datetime datetime= @date;  
  
SELECT @date AS '@date', @datetime AS '@datetime';  
  
--Result  
--@date      @datetime  
------------ -----------------------  
--2025-12-10 2025-12-10 00:00:00.000  
--  
--(1 row(s) affected)  

When the conversion is to smalldatetime, the date value is in the range of a smalldatetime, the date component is copied and the time component is set to 00:00:00.000. When the date value is outside the range of a smalldatetime value, error message 242 is raised: "The conversion of a date data type to a smalldatetime data type resulted in an out-of-range value.";and the smalldatetime value is set to NULL. The following code shows the results of converting a date value to a smalldatetime value.

DECLARE @date date= '1912-10-25';  
DECLARE @smalldatetime smalldatetime = @date;  
  
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@date      @smalldatetime  
------------ -----------------------  
--1912-10-25 1912-10-25 00:00:00  
--  
--(1 row(s) affected)  

For conversion to datetimeoffset(n), date is copied, and the time is set to 00:00.0000000 +00:00. The following code shows the results of converting a date value to a datetimeoffset(3) value.

DECLARE @date date = '1912-10-25';  
DECLARE @datetimeoffset datetimeoffset(3) = @date;  
  
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@date      @datetimeoffset  
------------ ------------------------------  
--1912-10-25 1912-10-25 00:00:00.000 +00:00  
--  
--(1 row(s) affected)  

When the conversion is to datetime2(n), the date component is copied, and the time component is set to 00:00.000000. The following code shows the results of converting a date value to a datetime2(3) value.

DECLARE @date date = '1912-10-25'  
DECLARE @datetime2 datetime2(3) = @date;  
  
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';  
  
--Result  
--@date      @datetime2(3)  
------------ -----------------------  
--1912-10-25 1912-10-25 00:00:00.000  
--  
--(1 row(s) affected)  

Converting String Literals to date

Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session. The following table shows the rules for converting a string literal to the date data type.

Input string literaldate
ODBC DATEODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into a date type will cause an implicit conversion between datetime and this type as defined by the conversion rules.
ODBC TIMESee previous ODBC DATE rule.
ODBC DATETIMESee previous ODBC DATE rule.
DATE onlyTrivial
TIME onlyDefault values are supplied.
TIMEZONE onlyDefault values are supplied.
DATE + TIMEThe DATE part of the input string is used.
DATE + TIMEZONENot allowed.
TIME + TIMEZONEDefault values are supplied.
DATE + TIME + TIMEZONEThe DATE part of local DATETIME will be used.

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 typeOutput
time12:35:29. 1234567
date2007-05-08
smalldatetime2007-05-08 12:35:00
datetime2007-05-08 12:35:29.123
datetime22007-05-08 12:35:29. 1234567
datetimeoffset2007-05-08 12:35:29.1234567 +12:15

CAST and CONVERT (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft