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:



OUTPUT

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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook