Click here to Skip to main content
15,612,080 members
Articles / DevOps
Posted 22 Sep 2017

Tagged as



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

Rate me:
Please Sign up or sign in to vote.
1.94/5 (5 votes)
27 Sep 2017CPOL2 min read
Things worth considering when using SQL Date and Time data types


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


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 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 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).




This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Database Developer
United Kingdom United Kingdom
Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).

Comments and Discussions

PraiseValuable information. Pin
SteveHolle27-Sep-17 7:30
SteveHolle27-Sep-17 7:30 
GeneralRe: Valuable information. Pin
Haroon Ashraf27-Sep-17 12:16
Haroon Ashraf27-Sep-17 12:16 
GeneralMy vote of 1 Pin
User 422140124-Sep-17 22:01
User 422140124-Sep-17 22:01 
GeneralRe: My vote of 1 Pin
SteveHolle25-Sep-17 4:14
SteveHolle25-Sep-17 4:14 
It's not all about you man.

GeneralRe: My vote of 1 Pin
User 422140125-Sep-17 23:00
User 422140125-Sep-17 23:00 
GeneralRe: My vote of 1 Pin
Haroon Ashraf25-Sep-17 22:32
Haroon Ashraf25-Sep-17 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.