July 07, 2011

SQL Server: DateTime vs DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue. The precision with a datetime2 is upto 100 nanoseconds.

Here’s an example:




As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds. However the datetime2 has a larger date range, a larger default fractional precision, and optional user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds. The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned earlier in case of datetime is 00:00:00 through 23:59:59.997 whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

The MSDN documentation also recommends datetime2 over datetime:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

ipad2 keyboard said...

I believe we should follow MSDN recommendation. So, use datetime2 in your scripts to be safeguarded against future changes.