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

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
   / @aarvikitchen5572  

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.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
   / kudvenkatarabic