Conversion from string to datetime2, time, date is timezone un-awared

  • Article

In SQL Server 2008, An instance of DateTimeOffset type can represent:

1) A local time + a time zone offset

2) A UTC time + a time zone offset

3) A local time and a UTC time.

When converting such an instance to other date and time types, such as datetime type, we will use the local time and convert it to corresponding target type. User can use style 1 in the convert function which will use the UTC time instead. For example:

declare @a datetimeoffset(0) ='2008-08-27 11:44:55 -07:00'

select CONVERT(datetime,@a) as local_time,CONVERT(datetime,@a,1) as utc_time

will return

local_time utc_time

----------------------- -----------------------

2008-08-27 11:44:55.000 2008-08-27 18:44:55.000

An important side impact for this is that assigning strings to other datetime types will be timezone unawared, i.e., the timezone part is ignored. For example,

declare @localtime datetime2(0) ='2008-08-27 11:44:55 -07:00'

declare @utctime datetime2(0) =convert(datetime2(0), cast('2008-08-27 11:44:55 -07:00' as datetimeoffset),1)

select @localtime, @utctime

localtime utctime

---------------------- ----------------------

2008-08-27 11:44:55 2008-08-27 18:44:55

The reason is that all new date and time types share the same string conversion code, and we first convert the input string to datetimeoffset(7) and use the default conversion routine to convert the value to the target type.