Difference between DateTime and DateTime2 in SQL Server

datetime vs datetime2 sql server

In this video we will discuss the difference between DateTime and DateTime2 in SQL Server

The date range for DateTime is January 1, 1753, through December 31, 9999 where as for DateTime2 it is January 1, 0001, through December 31, 9999

The time range for DateTime is 00:00:00 through 23:59:59.997 where as for DateTime2 it is 00:00:00 through 23:59:59.9999999

Accuracy for DateTime is 3.33 Milli-seconds where as for DateTime2 it is 100 nanoseconds

Storage size for DateTime is 8 Bytes where as for DateTime2 it is 6 to 8 Bytes (Depending on the precision required)

The default value for both DateTime and DateTime2 is the same – 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

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-datetime-and_21.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-datetime-and_20.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists