date (Transact-SQL) - SQL Server (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

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

date description

Supported string literal formats for date

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

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

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.

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 (Transact-SQL).

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 DATE = '12-10-25';DECLARE @datetime DATETIME = @date;SELECT @date AS '@date', @datetime AS '@datetime';

Here is 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 DATE = '1912-10-25';DECLARE @smalldatetime SMALLDATETIME = @date;SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';

Here is 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 DATE = '1912-10-25';DECLARE @datetimeoffset DATETIMEOFFSET(3) = @date;SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';

Here is 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 DATE = '1912-10-25'DECLARE @datetime2 DATETIME2(3) = @date;SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';

Here is 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.

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 is the result set.

See also

• CAST and CONVERT (Transact-SQL)