SQL Tips by Namwar Rizvi

August 29, 2007

100 Nano seconds precision in SQL Server 2008 DATE/TIME data types

Filed under: Information,New Features,SQL Server 2008 — namwar @ 9:50 PM

Today’s scientist use highly precise instruments and take measurements of time at nano seconds level. This level of precision needs a storage system which can provide storage of time and date data at nano seconds level and can easily scale up with future needs.
SQL Server 2008 has introduced a completely updated set of Date and Time data types which allows you to store Date and Time data separately or combined with variable precision settings. It allows you to record scientific readings at nano second level. Following is the list of Date and Time data types available in SQL Server 2008

Data Type





00:00:00.0000000 through 23:59:59.9999999

100 nano seconds

ANSI-compliant date data type


00001-01-01 through 9999-12-31

1 day

ANSI-compliant time data type with variable precision


1900-01-01 through 2079-06-06

1 minute


1753-01-01 through 9999-12-31

0.333 seconds


0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

100 nano seconds

like DATETIME, but with variable precision and large date range


00001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)

100 nano seconds

timezone aware/preserved datetime


1 Comment »

  1. Isn’t DateTime accurate to 0.003 (1/300) seconds?

    Comment by sqlservergoogler — January 5, 2012 @ 12:50 AM | Reply

RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: