Difference between DateTime and DateTime2 in SQL Server
| Attribute | DateTime | DateTime2 |
| Date Range | January 1, 1753, through December 31, 9999 | January 1, 0001, through December 31, 9999 |
| Time Range | 00:00:00 through 23:59:59.997 | 00:00:00 through 23:59:59.9999999 |
| Accuracy | 3.33 Milli-seconds | 100 nanoseconds |
| Size | 8 Bytes | 6 to 8 Bytes (Depends on the precision) |
| Default Value | 1900-01-01 00:00:00 | 1900-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
