SQL Server 2008 introduced several new data types and DateTime2 is one among them. In the earlier versions of SQL Server developers had only one data type option to save date & time values in database – DateTime. Now with the introduction of DateTime2, developer are given two options and this post explains what is the difference between these data types and which one to choose.
DateTime vs DateTime2
It’s important to understand the differences between these data types before we make a decision on which one to use. Both the data types, DateTime and DateTime2, lets us store data and time values but here are the differences between them
Storage: DateTime2 occupies 6 to 8 bytes of storage space depending on the time precision, where as DateTime always occupies 8 bytes. According to MSDN
[DateTime2 requires] 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.
Accuracy: With DateTime2 columns, you can store time values with precision of 100 nano-seconds. Where as DateTime columns precision is 333 nano-seconds.
Range: The minimum and maximum range of date values that can be stored in DateTime2 columns are 0001/01/01 – 9999/12/31. DateTime range is between 1753/01/01 and 9999/12/31. DateTime2 provides much improved minimum range values.
Configuring Precision: DateTime2 lets you configure precision of time part while declaring the fields but DateTime does not have any such provision.
Compliance: DateTime type is not ANSI or ISO compliant but DateTime2 is ANSI and ISO 8601 compliant.
Which One To Choose? DateTime or DateTime2?
From the above difference or rather advantages provided by DateTime2 it’s obvious that we have to use DateTime2 date type instead of DateTime. The MSDN page boldly highlights that we have to use DateTime2 instead of 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. datetimeoffsetprovides time zone support for globally deployed applications.
So next time when you are going to create a table column in SQL Server 2008+ to store date and time you are advised to use DateTime2 instead of DateTime. May if your application needs backward compatibility with earlier versions of SQL Server then consider using DateTime otherwise say bye bye to that data type.