In this tutorial, let use learn the difference between DateTime2 Vs DateTime. Both of these data types store both date & time. DateTime is the most popular Data Type for storing the date & Time. Microsoft introduced the DateTime2 data type in SQL 2008. It also recommends not use the DateTime & SmallDateTime in new projects. Let us see the differences between them in this article
Table of Contents
DateTime2 Vs DateTime
The following table lists some of the major difference between DateTime2 vs DateTime.
| DateTime2 | DateTime | |
|---|---|---|
| Syntax | datetime2(n) Where n is fractional seconds precision | datetime |
| Usage | DECLARE @MyDatetime2 datetime2(3) CREATE TABLE Table1 ( Column1 datetime2(7) ) | DECLARE @MyDatetime datetime CREATE TABLE Table1 ( Column1 datetime ) |
| ANSI SQL Compliant | SQL Standards and is ISO Compliant (ISO 8601) | No |
| Format | YYYY-MM-DD hh-mm-ss.nnnnnnn | YYYY-MM-DD hh-mm-ss.nnn |
| Date Range | 0001-01-01 To 1999-12-31 | 1753-01-01 To 1999-12-31 |
| Time Range | 00:00:00 through 23:59:59.9999999 | 00:00:00 through 23:59:59.997 |
| Accuracy | .0000001 seconds (100 nano seconds ) | Rounded to increments of .000, .003, or .007 seconds |
| User Defined Precision | Yes | No |
| Storage | 6 to 8 Bytes (Depends on no of fractional position) n <= 2 6 Bytes n =3 or n=4 7 Bytes n >=5 8 Bytes | 8 Bytes |
| Time zone offset | No | No |
| Default Value | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 |
| Implicit conversion | number data types are implicitly converted to a DATETIME | You need to do the explicit conversion |
| Base Date | 0001-01-01 | 1900-01-01 |
| How stored | 4 Bytes for Date comes first & 4 Bytes for time comes later | Times comes first ( 3 to 5 bytes) & 3 bytes for date which comes later Both time & Date stored in reverse order |
| Date Function | getDate() Returns current date & Time in DateTime format | SYSDATETIME() Function returns current date & Time in DateTime2 format |
| Reference | DateTime2 | DateTime |
Implicit Conversion
This is one important aspect you must aware of when you are converting from DateTime to DateTime2.
SQL Server automatically tries to convert one data type to another during evaluation of expressions. This is known as Implicit conversion and it is not visible to the user.
In case of Expressions involving DateTime & numeric data types, SQL Server does an implicit data conversion. For Example in the following expression @Dt+1 will result incrementing date by 1.
1 2 3 4 5 6 7 8 | Declare @Dt as DateTime Set @Dt='2020-12-26' Select @Dt+1 ----------------------- 2020-12-27 00:00:00.000 |
Implicit conversion does not happen between DateTime2 & numeric Data types. The following query will result in an error.
1 2 3 4 5 6 7 8 | Declare @Dt as DateTime2 Set @Dt='2020-12-26' Select @Dt+1 ------------------------------------------------------ Operand type clash: datetime2 is incompatible with int |
You may need to rewrite all such queries. In the above example, you can make use of the DateAdd function.
1 2 3 4 5 6 7 8 9 | Declare @Dt as DateTime2 Set @Dt='2020-12-26' Select Dateadd("d",1, @Dt) ----------------------- 2020-12-27 00:00:00.000 |
How Date & Time are Stored
Both Data Types uses the number to store date & Time.
The date integer stores the no of days elapsed since a base date. The base date for DateTime is 1900-01-01. For the DateTime2 it is 0001-01-01
Time integer stores the no of clock ticks since midnight. For DateTime each tick is 1⁄10000000 of a second. While for DateTime2 it is 1⁄300 of a second.
DateTime
Datetime uses fixed 8 bytes for storage, 4 Bytes for the date part & 4 Bytes for the Time part.
1 2 3 4 5 6 7 8 9 | DECLARE @dt DATETIME = '2020-12-26 12:20:15'; SELECT CAST(@dt as varbinary(8)); ------------------ 0x0000AC9D00CB50D4 -------- |
Where first 4 bytes 0000AC9D (44189) is the date part & the next 4 bytes 00CB50D4 (13324500) is the time part.
Add the 44189 to the base date, you will get our original date
1 2 3 4 5 6 7 8 9 | Declare @Dt as DateTime Set @Dt='1900-01-01' Select @Dt+44189 ----------------------- 2020-12-26 00:00:00.000 |
Similarly you can calculate the time.
Total No of seconds = (12Hr *60*60) + (20 minutes* 60) + (15 seconds)= 44415 seconds. 44415* 300 will give you 13324500, which is our time part.
DateTime2
1 2 3 4 5 6 7 | DECLARE @dt DATETIME2 = '2020-12-26 12:20:15'; SELECT CAST(@dt as varbinary(9)); -------------------- 0x078029676967F8410B |
The date part is last 3 bytes. F8410B. Even here the date is stored in reverse order. Hence F8 41 0B becomes 0B 41 F8, which is 737784 in decimal. Add it to the base year, which is 0001-01-01 and you will get our original date
1 2 3 4 5 6 7 8 | Declare @Dt as DateTime2 Set @Dt='0001-01-01' Select Dateadd("d",737784,@Dt) --------------------------- 2020-12-26 00:00:00.0000000 |
The time part is 8029676967 stored in reverse order. 6769672980 , which is 444150000000 in decimal
Total No of seconds = (12Hr *60*60) + (20 minutes* 60) + (15 seconds)= 44415 seconds. 44415* 10000000 will give you 444150000000, which is our time part.
The First most byte 07 is used to store the precision, which is 7 (Because we used DateTime2(7)) . The SQL server does not store the precision in database as it is part of the column definition.
Date Range
DateTim2 has a bigger date range of 0001/01/01 through 9999/12/31, While the DateTime type only supports year 1753/01/01 to 9999/01/01.
Precision
The Precision of DateTime2 is 1⁄10000000 of a second. The lowest unit of time that you can store is 0.0000001 second. Precision of DateTime is 1⁄300 of a second. The lowest unit of time that you can store is .003 second.
User Specified Precision
With Datetime2 you can also choose your fractional seconds precision. The DateTime comes with fixed precision. The DateTime2(3) is the closet equivalent to the DateTime
Storage
The DateTime2 data type occupies less storage compared to the DateTime. The DateTime2(3), which is equivalent to DateTime in terms of precision requires 7 bytes. The DateTime2(0) uses only 6 bytes, stores up to seconds (without any fractions) is good for most situations.
ISO Compliant
The DateTime2 adheres to the SQL Standards and is ISO Compliant (ISO 8601). Adhering these standards ensure that they are interpreted unambiguously when data is ported to different systems or regions etc.
Oh really?
Date Range 0001-01-01 To 1999-12-31 1753-01-01 To 1999-12-31
How about 9999 – otherwise you’re in Prince territory!!!