Saturday, February 8, 2014

SQL Server DateTime data types, ranges, accuracy and default values

SQL Server offers different useful datetime data types for different purposes. Knowing all and their capabilities would definitely help you to decide the type to be used. Here are the most important properties of them. Click on the links for more information.

Data type Range from to Default value Accuracy
Date
(1-3 bytes )
0001-01-01 9999-12-31 1900-01-01 One day
Datetime
(8 bytes)
1753-01-01 9999-12-31 1900-01-01 00:00:00.000 Rounded to increments of .000, .003, or .007 seconds
Datetime2
(6-8 bytes)
0001-01-01 9999-12-31 1900-01-01 00:00:00.0000000 100 nanoseconds
datetimeoffset
(10 bytes)
0001-01-01 9999-12-31 1900-01-01 00:00:00.0000000 +00:00 100 nanoseconds
Smalldatetime
(4 bytes)
1900-01-01 2076-06-06 1900-01-01 00:00:00 One minute
Time
(5 bytes)
00:00:00.0000000 23:59:59.9999999 00:00:00.0000000 100 nanoseconds

No comments: