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' AS datetime2(7)) AS 'datetime2',

CAST('2014-01-11 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset',

CAST('2014-01-11 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601';

Hope you Like it

Posted By: MR. JOYDEEP DAS