Difference between DateTime and DateTime2 in SQL Server

Difference between DateTime and DateTime2 in SQL Server

Date RangeJanuary 1, 1753, through December 31, 9999January 1, 0001, through December 31, 9999
Time Range00:00:00 through 23:59:59.99700:00:00 through 23:59:59.9999999
Accuracy3.33 Milli-seconds100 nanoseconds
Size8 Bytes6 to 8 Bytes (Depends on the precision)
Default Value1900-01-01 00:00:001900-01-01 00:00:00

DATETIME2 has a bigger date range than DATETIME. Also, DATETIME2 is more accurate than DATETIME. So I would recommend using DATETIME2 over DATETIME when possible. I think the only reason for using DATETIME over DATETIME2 is for backward compatibility.

DateTime2 Syntax : DATETIME2 [ (fractional seconds precision) ]

With DateTime2 

  • Optional fractional seconds precision can be specified
  • The precision scale is from 0 to 7 digits
  • The default precision is 7 digits
  • For precision 1 and 2, storage size is 6 bytes
  • For precision 3 and 4, storage size is 7 bytes
  • For precision 5, 6 and 7, storage size is 8 bytes

About the author


Leave a Comment