Rounding of datetime fractional second precision

= timestamp (time + date)

is the value of the escape sequence.

must follow

these formats for each

:

:

:

:

values are rounded to increments of

,

, or

seconds, as shown in the

following example.

Here’s the result set.

Expand table

Expand table

User-specified value

System stored value

ts

<constant_value>

<constant_value>

<literal_type>

d

yyyy-MM-dd t

hh:mm:ss[.fff]
ts

yyyy-MM-dd HH:mm:ss[.fff]

{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }

01/01/2024 23:59:59.999
2024-01-02 00:00:00.000

SELECT
'01/01/2024 23:59:59.999'
AS
[
User
-specified value
],
CAST (
'01/01/2024 23:59:59.999'
AS
DATETIME)
AS
[
System stored value
]
UNION
SELECT
'01/01/2024 23:59:59.998'
,
CAST (
'01/01/2024 23:59:59.998'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.997'
,
CAST (
'01/01/2024 23:59:59.997'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.996'
,
CAST (
'01/01/2024 23:59:59.996'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.995'
,
CAST (
'01/01/2024 23:59:59.995'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.994'
,
CAST (
'01/01/2024 23:59:59.994'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.993'
,
CAST (
'01/01/2024 23:59:59.993'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.992'
,
CAST (
'01/01/2024 23:59:59.992'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.991'
,
CAST (
'01/01/2024 23:59:59.991'
AS
DATETIME)
UNION
SELECT
'01/01/2024 23:59:59.990'
,
CAST (
'01/01/2024 23:59:59.990'
AS
DATETIME);