Difference between DateTime and DateTime2 in SQL Server

In this video we will discuss the difference between DateTime and DateTime2 in SQL Server

Differences between DateTime and DateTime2

Attribute

DateTime

DateTime2

Date Range

January 1, 1753, through December 31, 9999

January 1, 0001, through December 31, 9999

Time Range

00:00:00 through 23:59:59.997

00:00:00 through 23:59:59.9999999

Accuracy

3.33 Milli-seconds

100 nanoseconds

Size

8 Bytes

6 to 8 Bytes (Depends on the precision)

Default Value

1900-01-01 00:00:00

1900-01-01 00:00:00

DATETIME2 has a bigger date range than DATETIME. Also, DATETIME2 is more accurate than DATETIME. So I would recommend using DATETIME2 over DATETIME when possible. I think the only reason for using DATETIME over DATETIME2 is for backward compatibility.

DateTime2 Syntax :DATETIME2 [ (fractional seconds precision) ]

With DateTime2

Optional fractional seconds precision can be specified

The precision scale is from 0 to 7 digits

The default precision is 7 digits

For precision 1 and 2, storage size is 6 bytes

For precision 3 and 4, storage size is 7 bytes

For precision 5, 6 and 7, storage size is 8 bytes

The following script creates a table variable with 7 DATETIME2 columns with different precision start from 1 through 7

DECLARE@TempTableTABLE

(

DateTime2Precision1DATETIME2(1),

DateTime2Precision2DATETIME2(2),

DateTime2Precision3DATETIME2(3),

DateTime2Precision4DATETIME2(4),

DateTime2Precision5DATETIME2(5),

DateTime2Precision6DATETIME2(6),

DateTime2Precision7DATETIME2(7)

)

Insert DateTime value into each column

INSERTINTO@TempTableVALUES

(

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567',

'2015-10-20 15:09:12.1234567'

)

The following query retrieves the prcision, the datetime value, and the storage size.