date (Transact-SQL)

Article
10/08/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

Defines a date in SQL Server. The date data type was introduced in SQL Server 2008 (10.0.x).

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.
Range	`0001-01-01` through `9999-12-31` (`1582-10-15` through `9999-12-31` for Informatica) January 1, 1 CE (Common Era) through December 31, 9999 CE (October 15, 1582 CE through December 31, 9999 CE for Informatica)
Element ranges	`yyyy` is four digits from `0001` to `9999` that represent a year. Informatica limits `yyyy` 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, which represents a day of the specified month.
Character length	10 positions
Precision, scale	`10, 0`
Storage size	3 bytes, fixed
Storage structure	one 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 lists show the valid string literal formats for the date data type.

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

Only four-digit 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 server configuration option.

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 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 current language setting determines the default date format. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements.

The ydm format isn't supported for date.

String literal formats for month-day-year

SET DATEFORMAT mdy;

[m]m/dd/[yy]yy
[m]m-dd-[yy]yy

String literal formats for month-year-day

SET DATEFORMAT myd;

[m]m/[yy]yy/dd
[m]m-[yy]yy-dd
[m]m.[yy]yy.dd

String literal formats for day-month-year

SET DATEFORMAT dmy;

dd/[m]m/[yy]yy
dd-[m]m-[yy]yy
dd.[m]m.[yy]yy

String literal formats for day-year-month

SET DATEFORMAT dym;

dd/[yy]yy/[m]m
dd-[yy]yy-[m]m
dd.[yy]yy.[m]m

String literal formats for year-month-day

SET DATEFORMAT ymd;

[yy]yy/[m]m/dd
[yy]yy-[m]m-dd
[yy]yy-[m]m-dd

Alphabetical list of formats

[dd] mon[,] yyyy
dd mon[,][yy]yy
dd [yy]yy mon
[dd] yyyy mon
mon [dd][,] yyyy
mon dd[,] [yy]
mon yyyy [dd]
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 list of formats

yyyy-MM-dd
yyyyMMdd

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

Unseparated list of formats

[yy]yyMMdd
yyyy[MMdd]

The date data can be specified with four, six, or eight digits. A six-digit 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 the year.

ODBC date format

{ d 'yyyy-MM-dd' }

ODBC API specific.

W3C XML date format

yyyy-MM-ddTZD

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, which represent the number of hours in the time zone offset.
mm is two digits, ranging from 0 to 59, which represent the number of additional minutes in the time zone offset.
+ (plus) or - (minus) is the mandatory sign of the time zone offset. This sign indicates that, to obtain the local time, the time zone offset is added or subtracted from the Coordinated Universal Times (UTC) 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:

Datetime data types 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 that is defined as yyyy-MM-dd. This format is the same as the ISO 8601 definition for DATE.

Note

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

Backward compatibility for down-level clients

Some down-level clients don't 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 type	Default string literal format passed to down-level client	Down-level ODBC	Down-level OLEDB	Down-level JDBC	Down-level SQLCLIENT
time	`hh:mm:ss[.nnnnnnn]`	`SQL_WVARCHAR` or `SQL_VARCHAR`	`DBTYPE_WSTR` or `DBTYPE_STR`	`Java.sql.String`	`String` or `SqString`
date	`yyyy-MM-dd`	`SQL_WVARCHAR` or `SQL_VARCHAR`	`DBTYPE_WSTR` or `DBTYPE_STR`	`Java.sql.String`	`String` or `SqString`
datetime2	`yyyy-MM-dd HH:mm:ss[.nnnnnnn]`	`SQL_WVARCHAR` or `SQL_VARCHAR`	`DBTYPE_WSTR` or `DBTYPE_STR`	`Java.sql.String`	`String` or `SqString`
datetimeoffset	`yyyy-MM-dd HH:mm:ss[.nnnnnnn] [+ or -]hh:mm`	`SQL_WVARCHAR` or `SQL_VARCHAR`	`DBTYPE_WSTR` or `DBTYPE_STR`	`Java.sql.String`	`String` or `SqString`

Convert date and time data

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

Convert date to other date and time types

This section describes what occurs when you convert a date data type 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, the date component is copied. The following code shows the results of converting a date value to a datetime value.

DECLARE @date AS DATE = '12-10-25';

DECLARE @datetime AS DATETIME = @date;

SELECT @date AS '@date',
       @datetime AS '@datetime';

Here's the result set.

@date      @datetime
---------- -----------------------
2025-12-10 2025-12-10 00:00:00.000

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, and the smalldatetime value is set to NULL:

The conversion of a date data type to a smalldatetime data types resulted in an out-of-range value.

The following code shows the results of converting a date value to a smalldatetime value.

DECLARE @date AS DATE = '1912-10-25';

DECLARE @smalldatetime AS SMALLDATETIME = @date;

SELECT @date AS '@date',
       @smalldatetime AS '@smalldatetime';

Here's the result set.

@date      @smalldatetime
---------- -------------------
1912-10-25 1912-10-25 00:00:00

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 AS DATE = '1912-10-25';

DECLARE @datetimeoffset AS DATETIMEOFFSET (3) = @date;

SELECT @date AS '@date',
       @datetimeoffset AS '@datetimeoffset';

Here's the result set.

@date      @datetimeoffset
---------- ------------------------------
1912-10-25 1912-10-25 00:00:00.000 +00:00

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 AS DATE = '1912-10-25';

DECLARE @datetime2 AS DATETIME2 (3) = @date;

SELECT @date AS '@date',
       @datetime2 AS '@datetime2(3)';

Here's the result set.

@date      @datetime2(3)
---------- -----------------------
1912-10-25 1912-10-25 00:00:00.000

Convert string literals to date

Conversions from string literals to date and time types are allowed if all parts of the strings are in valid formats. Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that don't specify a style, from date and time types to string literals, are 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 literal	date
ODBC DATE	ODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into a date type causes an implicit conversion between datetime and the type that the conversion rules define.
ODBC TIME	See previous ODBC DATE rule.
ODBC DATETIME	See previous ODBC DATE rule.
DATE only	Trivial
TIME only	Default values are supplied.
TIMEZONE only	Default values are supplied.
DATE + TIME	The DATE part of the input string is used.
DATE + TIMEZONE	Not allowed.
TIME + TIMEZONE	Default values are supplied.
DATE + TIME + TIMEZONE	The DATE part of local DATETIME is used.

Examples

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

SELECT CAST ('2022-05-08 12:35:29.1234567 +12:15' AS TIME (7)) AS 'time',
       CAST ('2022-05-08 12:35:29.1234567 +12:15' AS DATE) AS 'date',
       CAST ('2022-05-08 12:35:29.123' AS SMALLDATETIME) AS 'smalldatetime',
       CAST ('2022-05-08 12:35:29.123' AS DATETIME) AS 'datetime',
       CAST ('2022-05-08 12:35:29.1234567 +12:15' AS DATETIME2 (7)) AS 'datetime2',
       CAST ('2022-05-08 12:35:29.1234567 +12:15' AS DATETIMEOFFSET (7)) AS 'datetimeoffset';

Here's the result set.

Data type	Output
time	`12:35:29.1234567`
date	`2022-05-08`
smalldatetime	`2022-05-08 12:35:00`
datetime	`2022-05-08 12:35:29.123`
datetime2	`2022-05-08 12:35:29.1234567`
datetimeoffset	`2022-05-08 12:35:29.1234567 +12:15`

CAST and CONVERT (Transact-SQL)

Share via

date (Transact-SQL)

date description

Supported string literal formats for date

String literal formats for month-day-year

String literal formats for month-year-day

String literal formats for day-month-year

String literal formats for day-year-month

String literal formats for year-month-day

Alphabetical list of formats

ISO 8601 list of formats

Unseparated list of formats

ODBC date format

W3C XML date format

ANSI and ISO 8601 compliance

Backward compatibility for down-level clients

Convert date and time data

Convert date to other date and time types

Convert string literals to date

Examples

Feedback

Additional resources

Share via

date (Transact-SQL)

date description

Supported string literal formats for date

String literal formats for month-day-year

String literal formats for month-year-day

String literal formats for day-month-year

String literal formats for day-year-month

String literal formats for year-month-day

Alphabetical list of formats

ISO 8601 list of formats

Unseparated list of formats

ODBC date format

W3C XML date format

ANSI and ISO 8601 compliance

Backward compatibility for down-level clients

Convert date and time data

Convert date to other date and time types

Convert string literals to date

Examples

Related content

Feedback

Additional resources