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
|
N
|
Y
|
Y
|
Y
|
date
|
N
|
N
|
Y
|
Y
|
Y
|
smalldatetime
|
Y
|
Y
|
Y
|
Y
|
Y
|
datetime
|
Y
|
Y
|
Y
|
Y
|
Y
|
datetime2
|
N
|
N
|
Y
|
Y
|
Y
|
datetimeoffset
|
N
|
N
|
Y
|
Y
|
Y
|
Hope you like it.
