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?
Best Answer
You might want to consider looking at the following page:
Ther you will find a link to the following information:
The
datetimeoffset
data type is defined as:An example is given as follows:
You might also want to look at the AT TIME ZONE (Transact-SQL) function when referencing a
datetimeoffset
value: