Share via

Colon(:) and dot (.) in factional second is not the same

  • Article

Colon and Dot are two symbol to separate the second and factional second in a given time string. If we create following queries:

select CAST('1999-12-21 01:01:01.12' as datetime)

-----------------------

1999-12-21 01:01:01.120

select CAST('1999-12-21 01:01:01:12' as datetime)

-----------------------

1999-12-21 01:01:01.013

As you can see, we have different result for colon and dot separtated string. In the example, the .12 means 0.12 seconds, however :12 means 12 units (while each unit is 1/300 second for datetime. You may wonder how do these symbol apply to the variable fractional second types, such as datetime2, time,etc. Here is the sample query and result

select CAST('1999-12-21 01:01:01.1234' as datetime2(6))

-----------------------

1999-12-21 01:01:01.123400

select CAST('1999-12-21 01:01:01:1234' as datetime2(6))

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

As you can see, the dot represenation continue works as we expected, but the colon string will raise an error. As a recommandation, user should use dot as the separator for second and factional second, and which is also the requirement of ISO 8601 standard.