Below are the string literal date formats that SQL Server supports for the datetime2 data type. These are the formats that we can provide when setting a datetime2 date from a string literal.

SQL Server supports two string literal formats for the datetime2 data type:

Compatibility	Format / Syntax	Examples
ISO 8601	YYYY-MM-DDThh:mm:ss[.nnnnnnn]	2035-12-20T17:15:45.1234567 2035-12-20T17:15:45
ODBC	{ ts ‘yyyy-mm-dd hh:mm:ss[.fractional seconds]’ }	{ ts ‘2035-12-20 17:15:45.1234567’ } { ts ‘2035-12-20 17:15:45.123’ } { ts ‘2035-12-20 17:15:45’ }

The square brackets indicate that the fractional seconds are optional.

Examples

Below are examples of how these work.

ISO 8601

Here’s an example of providing ISO 8601 compliant string literals:

SELECT 
    CAST('2035-12-20T17:15:45.1234567' AS datetime2(7)) AS "Result 1",
    CAST('2035-12-20T17:15:45' AS datetime2(7)) AS "Result 2",
    CAST('2035-12-20' AS datetime2(7)) AS "Result 3",
    CAST('17:15:45' AS datetime2(7)) AS "Result 4";

Result:

Result 1	                Result 2	                Result 3	               Result 4
------------------------------  ------------------------------  -----------------------------  ------------------------------
2035-12-20 17:15:45.1234567	2035-12-20 17:15:45.0000000	2035-12-20 00:00:00.0000000    1900-01-01 17:15:45.0000000

ODBC

Here’s an example of providing string literals in ODBC format:

SELECT 
    CAST( { ts '2035-12-20 17:15:45.123' } AS datetime2(7)) AS "Result 1",
    CAST( { ts '2035-12-20 17:15:45' }  AS datetime2(7)) AS "Result 2",
    CAST( { d '2035-12-20' }  AS datetime2(7)) AS "Result 3",
    CAST( { t '17:15:45' }  AS datetime2(7)) AS "Result 4";

Result:

Result 1	                Result 2	                Result 3	                Result 4
------------------------------  ------------------------------  ------------------------------  ---------------------------
2035-12-20 17:15:45.1233333	2035-12-20 17:15:45.0000000	2035-12-20 00:00:00.0000000	2023-01-18 17:15:45.0000000

The square brackets in the above format indicate that the fractional seconds are optional. According to the Microsoft documentation:

The number of digits to the right of the decimal point, which represents the fractional seconds, can be specified from 0 up to 7 (100 nanoseconds).

However, the documentation also states:

ODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into datetime2 types will cause an implicit conversion between datetime and this type as defined by the conversion rules.

And it also states that the format is:

ODBC API specific

We may get a conversion error if the fractional seconds part is more than 3 digits. Here’s what happens when I increase the fractional seconds from the previous example to seven digits:

SELECT 
    CAST( { ts '2035-12-20 17:15:45.1234567' } AS datetime2(7)) AS "Result 1",
    CAST( { ts '2035-12-20 17:15:45' }  AS datetime2(7)) AS "Result 2";

Result:

Msg 241, Level 16, State 3, Line 2
Conversion failed when converting date and/or time from character string.

More Info

See the Microsoft documentation for more information.