SQL Server datetime column datatype stores offset

Question

Does the SQL Server datetime column have the capability to store the offset of the time?

I'd like to store a value like

YYYY-MM-DDThh:mm:ss±hh

e.g.

2015-07-14T10:08:15+01

But when I insert the above value in the datetime column, the error that SQL Server returns is as follows:

insert into TestTable(testdatetimeoffset) values ('2015-07-14T10:08:15+01')

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

I also tried

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15+01')

>

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

and

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15 01') 

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

If datetime is unable to support the offset, what are the best ways to do this?

Answer 1

You might want to consider looking at the following page:

• Date and Time Data Types and Functions (Transact-SQL)

Ther you will find a link to the following information:

• datetimeoffset (Transact-SQL)

The datetimeoffset data type is defined as:

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.

An example is given as follows:

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  
DECLARE @date date= @datetimeoffset;  

SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';  

@datetimeoffset                  date  
-------------------------------- ----------  
2025-12-10 12:32:10.0000 +01:0   2025-12-10  

(1 row(s) affected)  

You might also want to look at the AT TIME ZONE (Transact-SQL) function when referencing a datetimeoffset value:

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.