datetimeoffset (T-SQL)
Introduction
A lot of query came to me reading datetimeoffset in MS SQL 2008.
This is a special data type introduced by MS from MS SQL Server Version 2008 and onwards. Previously we used datetime and now it added another keyword in the name of Offset.
It basically
Date + Time + Offset
The Datetime takes Date and Time portions and Offset takes the Time Zone.
In Microsoft Development Network
"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."
Syntax
datetimeoffset [ (fractional seconds precision) ]
How to Use it
DECLARE @MyDatetimeoffset datetimeoffset(7)
CREATE TABLE Table1
( Column1 datetimeoffset(7) )
Default Format
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
Date Range
0001-01-01 through 9999-12-31
January 1,1 A.D. through December 31, 9999 A.D.
Time Range
00:00:00 through 23:59:59.9999999
Time Zone 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.
Example
SELECT
CAST('2014-01-11 12:35:29. 1234567 +12:15' AS time(7))AS 'time',
CAST('2014-01-11 12:35:29. 1234567 +12:15' AS date) AS'date',
CAST('2014-01-11 12:35:29.123' AS smalldatetime) AS'smalldatetime',
CAST('2014-01-11 12:35:29.123' AS datetime) AS'datetime',
CAST('2014-01-11 12:35:29.1234567+12:15' ASdatetime2(7)) AS 'datetime2',
CAST('2014-01-11 12:35:29.1234567 +12:15' ASdatetimeoffset(7)) AS 'datetimeoffset',
CAST('2014-01-11 12:35:29.1234567+12:15' ASdatetimeoffset(7)) AS 'datetimeoffset IS08601';
