Exploring Further - Date and Time Data Types (SQL Server)

• Download source code.zip - 574 B

Introduction

This tip provides knowledge based understanding of Date and Time data types of SQL Server beyond their traditional day to day usage.

Background

Apart from declaring, using and comparing date and time variables when it comes to date time data types, the following five things matter:

1. Storage
2. Precision
3. Scale
4. Range
5. Accuracy

In a typical date time data type, we have two parts:

1. Date Part
2. Time Part

Date Part

Date part consists of Year, Month and Day for example, 2017-01-20 (20 Jan 2017).

Time Part

The Time part consists of Hour, Minute and Second and after that, fractional part of the seconds.

Comparing Different Date and Time Data Types

Let us now discuss different Date Time data types in terms of storage, precision, scale, etc.

DateTime

DateTime is old data type in SQL that occupies 8 bytes of storage with a fractional precision or accuracy upto 3 milliseconds (0.333 sec) ranging from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997.

This data type has an accuracy limitation of upto 3 milliseconds so not encouraged any more, rather it is recommended to either use DateTime2 data type or seperate Date and Time data types.

DateTime2

DateTime2 is a new data type in SQL that occupies 6 to 8 bytes of storage with a fractional precision or accuracy of up to 100 nano seconds ranging from 0001-01-01 00:00:00.0000000 to 9999-12-31.9999999.

In DateTime2 fractional seconds precision by default is 7 that takes 8 bytes of storage.

For fractional seconds precision less than 3 the storage size is 6 bytes.

For fractional seconds precision between 3 and 4 the storage size is 7 bytes.

For fractional seconds precision between 5 and 8 takes 8 bytes of storage.

DateTime2 Explicit Declarations

DateTime2(n) represents precision scale of the data type.

For example, DateTime2(2) has a precision scale of 2 and with fractional seconds, precision less than 3 digits and occupies 6 bytes of storage such as 2017-01-01 10:10:00.20.

If left undefined DateTime2 has a default precision scale from 0 to 7 digits with an accuracy of 100 nano seconds and takes 8 bytes of storage.

Coding Example

Let us take an example of the code below:

DECLARE @MyDate DATETIME2 -- Default fractional seconds precision: 7
SET @MyDate=DATETIME2FROMPARTS(2017,01,01,10,11,00,9999999,7)
SELECT @MyDate AS DateTime_Default_Precision_7

DECLARE @MyDate2 DATETIME2(2) -- fractional seconds precision: 2
SET @MyDate2=DATETIME2FROMPARTS(2017,01,01,10,11,00,99,2)
SELECT @MyDate2 AS DateTime_Default_Precision_2

DECLARE @MyDate3 DATETIME2(3) -- fractional seconds precision: 3
SET @MyDate3=DATETIME2FROMPARTS(2017,01,01,10,11,00,999,3)
SELECT @MyDate3 AS DateTime_Default_Precision_3

Coding Example-2 (DateTime2 as DateTime)

Let us take another example of using DateTime2 to serve the same purpose as DateTime:

DECLARE @StartDate DATETIME -- old style of date declaration
DECLARE @StartDate2 DATETIME2(3) -- new style of date declaration with same precision as DateTime

SET @StartDate='20 Jan 2017'
SET @StartDate2=DATETIMEFROMPARTS(2017,01,20,0,0,0,000) -- (20Jan2017) New constructor function for dates

SELECT @StartDate as StartDate_Using_DateTime
SELECT @StartDate2 as [StartDate_Using_DateTime2(3)]

Points of Interest

DateTime2 is even more efficient when used for the same requirements as of DateTime (accuracy upto 3 milliseconds).

When DateTime2 is defined as DateTime2 (3) has a precision scale of 3 and with fractional seconds precision 3 and occupies 7 bytes of storage which is less than the size occupied by old data type DateTime (8 bytes).

References

1. https://sqltips.wordpress.com/2007/08/29/100-nano-seconds-precision-in-sql-server-2008-datetime-data-types/
2. https://docs.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql
3. https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql
4. http://www.madeiradata.com/datetime-vs-datetime2/
5. https://docs.microsoft.com/en-us/sql/t-sql/functions/datetime2fromparts-transact-sql