Various time and date datatypes in SQL server overview

Reference: Robert Hartmann  on SQLSERVERCENTRAL.COM 

Various time and date datatypes in SQL server:

Type Format Range Accuracy Storage in bytes User defined precision
time hh:mm:ss [.nnnnnnn] 00:00:00.0000000
through
23:59:59.9999999
100 nanoseconds 3 to 5 Yes
date YYYY-MM-DD 0001-01-01 through
9999-12-31
1 day 3 No
smalldatetime YYYY-MM-DD
hh:mm:ss
1900-01-01 through
2079-06-06
1 minute 4 No
datetime YYYY-MM-DD
hh:mm:ss [.nnn]
1753-01-01 through
9999-12-31
0.00333 seconds 8 No
datetime2 YYYY-MM-DD
hh:mm:ss [.nnnnnnn]
0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
100 nanoseconds 6 to 8 Yes
datetimeoffset YYYY-MM-DD 0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
(int UTC)
100 nanoseconds 8 to 10
(2 for
zone)
Yes
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s