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

Range

Accuracy

Remarks

Time

00:00:00.0000000 through 23:59:59.9999999

100 nano seconds

ANSI-compliant date data type

Date

00001-01-01 through 9999-12-31

1 day

ANSI-compliant time data type with variable precision

Smalldatetime

1900-01-01 through 2079-06-06

1 minute

Datetime

1753-01-01 through 9999-12-31

0.333 seconds

Datetime2

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

Datetimeoffset

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

100 nano seconds

timezone aware/preserved datetime

Advertisements

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 )

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

Create a free website or blog at WordPress.com.

%d bloggers like this: