Thursday, August 8, 2013

All about Date Time Data Type

All about Date Time Data Type

Introduction

As I find many of the developer has a little bit confusion related to DATE and TIME data type of MS SQL Server. Here in this article I am trying to describe them all. Hope it will be helpful to understand them and clarify the knowledge.

In this article I am trying to discuss about data type

1.    DATETIME
2.    SMALLDATETIME
3.    DATE
4.    TIME
5.    DATETIME2
6.    DATETIMEOFFSET

DATETIME

This data type contains both DATE and TIME.

Date range
January 1, 1753, through December 31, 9999
Time Range
00:00:00 through 23:59:59.997
Element Range
YYYY is four digits from 1753 through 9999 that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Storage
8 Bytes

If we not specified the TIME in DATETIME data type it always 00:00:00.000. Lets taken an example of it

DECLARE @v_date DATE;
DECLARE @v_datetime DATETIME;

SET @v_date = '12-21-05';
SET @v_datetime = @v_date;

SELECT @v_datetime AS '@v_datetime', @v_date AS'@v_date';

Output

@v_datetime             @v_date
----------------------- ----------
2005-12-21 00:00:00.000 2005-12-21

If we set only the TIME component and NOT the DATE part, the DATE part will take 1900-01-01 by default.

DECLARE @v_time TIME;
DECLARE @v_datetime DATETIME;

SET @v_time = '12:10:05.1237';
SET @v_datetime = @v_time;

SELECT @v_datetime AS '@v_datetime', @v_time AS'@v_time';

@v_datetime             @v_time
----------------------- ----------------
1900-01-01 12:10:05.123 12:10:05.1237000

SMALLDATETIME

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Date range
January 1, 1900, through June 6, 2079
Time Range
00:00:00 through 23:59:59
Element Range
YYYY is four digits, ranging from 1900, to 2079, that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.
Storage
4 Bytes

Here is an example

DECLARE @v_smalldatetime SMALLDATETIME;
DECLARE @v_date DATE;

SET @v_smalldatetime = '1955-12-13 12:43:10';
SET @v_date = @v_smalldatetime;

SELECT @v_smalldatetime AS '@v_smalldatetime',
       @v_date AS '@v_date';

@v_smalldatetime        @v_date
----------------------- ----------
1955-12-13 12:43:00     1955-12-13

DATE

Define only DATE not TIME.

Date range
January 1, 1 A.D. through December 31, 9999 A.D.
Element Range
YYYY is four digits from 0001 to 9999 that represent a year.
MM is two digits from 01 to 12 that represent a month in the specified year.
DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Storage
3 Bytes

DECLARE @v_date  DATE,
        @v_date1 DATE;

SET @v_date  = '1955-12-13';
SET @v_date1 = '1955-12-13 12:10:05.1237';

SELECT @v_date AS '@v_date', @v_date1 AS '@v_date1';

@v_date    @v_date1
---------- ----------
1955-12-13 1955-12-13

Look here, in variable @v_date1 we specified time also but it just removes the TIME part and shows us only the DATE part.

TIME

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

Range
00:00:00.0000000 through 23:59:59.9999999
Element Range
hh is two digits, ranging from 0 to 23, that represent the hour.
mm is two digits, ranging from 0 to 59, that represent the minute.
ss is two digits, ranging from 0 to 59, that represent the second.
n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
Storage
5 Bytes

Example

DECLARE @v_timeFrom TIME(4);
DECLARE @v_timeTo   TIME(3);

SET @v_timeFrom = '12:34:54.1237';
SET @v_timeTo   = @v_timeFrom;

SELECT @v_timeTo AS 'v_time(3)',
       @v_timeFrom AS 'v_time(4)';

v_time(3)        v_time(4)
---------------- ----------------
12:34:54.124     12:34:54.1237

DECLARE @time4 TIME(4);
DECLARE @time7 TIME(7);
DECLARE @timeC TIME; 

SET @time4 = '12:32:51.1234';
SET @time7 = '23:32:51.1234567';
--will only take the time and second fraction as 7 
SET @timeC = '1955-12-13 19:21:55.123'

SELECT @time4 AS 'time(4)',
       @time7 AS 'time(7)',
       @timeC AS 'timeC';

time(4)          time(7)          timeC
---------------- ---------------- ----------------
12:32:51.1234    23:32:51.1234567 19:21:55.1230000


DATETIME2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Date Range
January 1,1 AD through December 31, 9999 AD
Time Range
00:00:00 through 23:59:59.9999999
Element Range
YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year.

MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year.

DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month.

hh is a two-digit number, ranging from 00 to 23, that represents the hour.

mm is a two-digit number, ranging from 00 to 59, that represents the minute.

ss is a two-digit number, ranging from 00 to 59, that represents the second.

n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds.
Storage
6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

Example

DECLARE @v_datetime2  DATETIME2(4);
DECLARE @v_date       DATE;

SET @v_datetime2 = '12-10-25 12:32:10.1234';
SET @v_date      = @v_datetime2;

SELECT @v_datetime2 AS '@v_datetime2',
       @v_date AS '@v_date';

@v_datetime2           @v_date
---------------------- ----------
2025-12-10 12:32:10.12 2025-12-10

DATETIMEOFFSET

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Date Range
January 1,1 A.D. through December 31, 9999 A.D.
Time Range
00:00:00 through 23:59:59.9999999
Offset Range
-14:00 through +14:00
Element Range
YYYY is four digits, ranging from 0001 through 9999, that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.

hh is two digits that range from -14 to +14.

mm is two digits that range from 00 to 59.

Storage
10 bytes, fixed is the default with the default of 100ns fractional second precision.

Example

DECLARE @v_datetimeoffset datetimeoffset(4);
DECLARE @v_time           time(3);
DECLARE @v_date           date;  

SET @v_datetimeoffset = '12-13-25 12:32:10 +05:30'; 
SET @v_time           = @v_datetimeoffset;
SET @v_date           = @v_datetimeoffset;   

SELECT @v_datetimeoffset AS '@v_datetimeoffset',
       @v_date AS '@v_date',
       @v_time AS '@v_time'; 

@v_datetimeoffset                  @v_date    @v_time
---------------------------------- ---------- ----------------
2025-12-13 12:32:10.0000 +05:30    2025-12-13 12:32:10.000

MS SQL Server Version wise
Data Type 
2000 
2005  
2008  
2008 R2  
2012  
time 
N  
Y  
Y  
Y  
date 
N  
Y  
Y  
Y  
smalldatetime 
Y  
Y  
Y  
Y  
datetime 
Y  
Y  
Y  
Y  
datetime2 
N  
Y  
Y  
Y  
datetimeoffset 
N  
Y  
Y  

Hope you like it.