May 19, 2017 at 11:12 am
Our application receives web service messages that contains two elements EffectiveTime and TimeZone. When EffectiveTime is provided as datetimeoffset we should ignore the value provided in TimeZone, and when EffectiveTime is provided as datetime (without Z or +hh:mm or -hh:mm) the time zone of EffectiveTime is provided in TimeZone. What would be the most efficient way to identify if EffectiveTime contains time zone offset information?
Currently, we are reading EffectiveTime as both a datetime/datetimeoffset and a string. We use regular expression to identify if the string contains the offset:select @hasOffset = case when @dtString like '%Z%' or @dtString like '%[-+][0-9][0-9]:[0-9][0-9]%' then 1 else 0 end
if @dtString is '2017-05-15T10:30:00-05:00', @hasOffset is 1 and I ignore the time zone and know the time is 15:30 UTC
if @dtString is '2017-05-15T10:30:00Z', @hasOffset is 1 and I ignore the time zone and know the time is 10:30 UTC
if @dtString is '2017-05-15T10:30:00', @hasOffset is 0 and I must use the value provided in element TimeZone to convert to UTC.
Basically, I'm looking for a more efficient way to convert EffectiveTime to UTC or any other time zone. Any help is appreciated.
May 21, 2017 at 1:13 am
The best that I can come up with is
cast(case substring(@dtString, 20, 1)
when '+' then dateadd(mi, -datediff(mi, 0, substring(@dtString, 21, 5)), substring(@dtString, 1, 19))
when '-' then dateadd(mi, datediff(mi, 0, substring(@dtString, 21, 5)), substring(@dtString, 1, 19))
else cast(@dtString as datetimeoffset)
end as datetime)
declare @d1 varchar(50) = '2017-05-15T10:30:00-05:00';
declare @d2 varchar(50) = '2017-05-15T10:30:00Z';
declare @d3 varchar(50) = '2017-05-15T10:30:00';
select
d1 = cast(case substring(@d1, 20, 1)
when '+' then dateadd(mi, -datediff(mi, 0, substring(@d1, 21, 5)), substring(@d1, 1, 19))
when '-' then dateadd(mi, datediff(mi, 0, substring(@d1, 21, 5)), substring(@d1, 1, 19))
else cast(@d1 as datetimeoffset)
end as datetime)
, d2 = cast(case substring(@d2, 20, 1)
when '+' then dateadd(mi, -datediff(mi, 0, substring(@d2, 21, 5)), substring(@d2, 1, 19))
when '-' then dateadd(mi, datediff(mi, 0, substring(@d2, 21, 5)), substring(@d2, 1, 19))
else cast(@d2 as datetimeoffset)
end as datetime)
, d3 = cast(case substring(@d3, 20, 1)
when '+' then dateadd(mi, -datediff(mi, 0, substring(@d3, 21, 5)), substring(@d3, 1, 19))
when '-' then dateadd(mi, datediff(mi, 0, substring(@d3, 21, 5)), substring(@d3, 1, 19))
else cast(@d3 as datetimeoffset)
end as datetime)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply