Search code examples
.netsql-serverdapper

Enforce the precision in datetime column

How can I set the precision in datetime SQL column so that the value stored is always yyyy-MM-dd HH:mm:ss i.e. without any fractional seconds?

I am using .NET and Dapper to insert values and although I know that in .NET there is no fractional second I am seeing in the database thats values have fractional seconds.

Solution

  • If you have a datetime2(0) there are no milliseconds

    Example

    Select NoMS   = convert(datetime2(0),getDate())
      ,WithMS = convert(datetime2(3),getDate())

    Returns

    NoMS                   WithMS
2020-06-18 20:06:21    2020-06-18 20:06:21.337

    EDIT: Just a word of caution:

    datetime2() will round up

    For Example

    Select NoMS   = convert(datetime2(0),'2020-06-19 07:33:57.500')
      ,WithMS = convert(datetime2(4),'2020-06-19 07:33:57.500')

    Returns

    NoMS                 WithMS
2020-06-19 07:33:58  2020-06-19 07:33:57.5000

    Notice 58 seconds vs 57.5000