DateTime2 Vs DateTime in SQL SERVER

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

DateTime2 Vs DateTime

The following table lists some of the major difference between DateTime2 vs DateTime.

 DateTime2DateTime
Syntaxdatetime2(n)

Where n is fractional seconds precision
datetime
UsageDECLARE @MyDatetime2 datetime2(3)

CREATE TABLE Table1 ( Column1 datetime2(7) )
DECLARE @MyDatetime datetime

CREATE TABLE Table1 ( Column1 datetime )
ANSI SQL CompliantSQL Standards and is ISO Compliant (ISO 8601)No
FormatYYYY-MM-DD hh-mm-ss.nnnnnnnYYYY-MM-DD hh-mm-ss.nnn
Date Range0001-01-01 To 1999-12-311753-01-01 To 1999-12-31
Time Range00:00:00 through 23:59:59.999999900:00:00 through 23:59:59.997
Accuracy.0000001 seconds (100 nano seconds )Rounded to increments of .000, .003, or .007 seconds
User Defined PrecisionYesNo
Storage6 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 offsetNoNo
Default Value1900-01-01 00:00:001900-01-01 00:00:00
Implicit conversionnumber data types are implicitly converted to a DATETIMEYou need to do the explicit conversion
Base Date0001-01-011900-01-01
How stored4 Bytes for Date comes first & 4 Bytes for time comes laterTimes comes first ( 3 to 5 bytes) & 3 bytes for date which comes later

Both time & Date stored in reverse order
ReferenceDateTime2DateTime

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.

Implicit conversion does not happen between DateTime2 & numeric Data types. The following query will result in an error.

You may need to rewrite all such queries. In the above example, you can make use of the DateAdd function.

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 110000000 of a second. While for DateTime2 it is 1300 of a second. 

DateTime

Datetime uses fixed 8 bytes for storage, 4 Bytes for the date part & 4 Bytes for the Time part.

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

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

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

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 110000000 of a second. The lowest unit of time that you can store is 0.0000001 second. Precision of DateTime is 1300 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 DateTime 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.

Reference

ISO 8601
SQL Standards

Leave a Comment

Your email address will not be published.

Scroll to Top