DateTime vs DateTime2 in SQL Server
In SQL Server, DateTime and DateTime2 are data types used to define a date combined with a time of day in a 24-hour clock format.
Microsoft recommends using DateTime2 instead of DateTime as it is more portable and provides more seconds precision. Also, DateTime2 has a larger date range and optional user-defined seconds precision with higher accuracy. Datetime2 aligns with SQL standards.
The key differences between DateTime and DateTime2 are listed in the table below
| Property | DateTime | DateTime2 |
|---|---|---|
| Syntax | DateTime | DateTime2[ ( n )] where n is fractional seconds precision from 0 to 7 |
| Format | YYYY-MM-DD hh:mm:ss.nnn | YYYY-MM-DD hh:mm:ss[.n] |
| Date range | January 1, 1753, through December 31, 9999 | through 9999-12-31 |
| Time range | 00:00:00 through 23:59:59.997 | 00:00:00 through 23:59:59.9999999 |
| Usage | DECLARE @MyDatetime datetime | DECLARE @MyDatetime2 datetime2(7) |
| Fractional seconds range | Zero to three digits, ranging from 0 to 999 represents the fractional seconds. | Zero- to seven-digit number from 0 to 9999999 represents the fractional seconds |
| Accuracy | Rounded to increments of .000, .003, or .007 seconds | 100 nanoseconds |
| Default value | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 |
| Character length | 19 positions minimum to 23 maximum | 19 positions minimum to 27 maximum |
| User-defined fractional second precision | No | Yes |
| Storage size | 8 bytes | 6 bytes for precision less than 3. |
| ANSI and ISO 8601 compliance | No | Yes |
The following example demonstrates the precision difference between DateTime and DateTime2.
DECLARE @datetime2 datetime2(7), @datetime datetime; SET @datetime2 = GETDATE(); SET @datetime = GETDATE(); SELECT @datetime AS 'datetime', @datetime2 AS 'datetime2';
In the above example, the precision of both DateTime and DateTime2 is displayed for the current date GETDATE (). Datetime2 has fractional seconds precision of 7 which means that there are 7 digits representing the nanosecond value whereas DateTime has a precision of 3.
The following example demonstrates the fractional seconds rounding off.
DECLARE @datetime datetime, @datetime2 datetime2(3); SET @datetime2 = '2022-11-23 10:45:30.6782222'; SET @datetime = @datetime2; SELECT @datetime AS 'datetime', @datetime2 AS 'datetime2';
Here, the datetime2 fractional seconds precision is set to 3 which is the same as that of datetime. Datetime2 is assigned a date string literal and DateTime is assigned DateTime2 variable value. In the result, the value for DateTime is rounded off to 677 and for DateTime2 it remains at 678
Note: You will get a conversion error if you directly assign a string literal with more than 3 fractional seconds precision for DateTime variable i.e., you get an error if you directly assign '2022-11-23 10:45:30.6782222' value to DateTime variable (@datetime) as its precision is 7.
Storage space of DateTime and DateTime2
The following example demonstrates the memory space occupies by DateTime and DateTime2 data types.
DECLARE @datetime datetime, @datetime2 datetime2(3); SET @datetime2 = GETDATE(); SET @datetime = GETDATE(); SELECT DATALENGTH(@datetime) AS 'datetime', DATALENGTH(@datetime2) AS 'datetime2';
Both DateTime and DateTime2 are passed with the current date GETDATE(). The DATALENGTH of both variables gives the size occupied by each data type. As you can see in the above result, DateTime occupies 8 bytes whereas DateTime2 with fractional seconds precision of 3, occupies 7 bytes.
Simple Math Operation on DateTime and DateTime2
It is important to note that there are a few drawbacks of using DateTime2. You cannot do basic math operations with dates of DateTime2 data type, like adding a number to a date. Whereas it works with a DateTime data type.
Below is an example to add a day to the current date for a DateTime and DateTime2 data types.
Add a day to DateTime data type. This works as expected and the result displays the next day.
DECLARE @datetime datetime; SET @datetime = GETDATE(); SELECT @datetime + 1 as Tomorrow;
Add a day to DateTime2 data type. This doesn't work like expected and you will get an error as shown in the result.
DECLARE @datetime2 datetime2(7); SET @datetime2 = GETDATE(); SELECT @datetime2 + 1 as Tomorrow;