Converting a SQL Server Timezone object `yyyy-mm-dd HH:MM;SS HH` to a valid datetime (UTC)-CodePudding

I have a string representation of datetime values which has an offset 03 I'm assuming this means 03 hours I most likely may be wrong.

This is a typical value in my table:

2015-05-15 21:35:19 03

there are only two unique values 3 or 2 I'm not sure what the source system is/was.

I wonder what the correct way to convert such an object is? my current method is to split the offset and convert the remainder to a datetime object and add the offset using dateadd

SELECT DATEADD(hour, CAST(value AS int)
          , CAST(REPLACE(dt, ' '   value, '') AS DATETIME2)) AS offset_val
, dt
FROM (

       VALUES ('2015-05-15 21:35:19 03')
            , ('2015-05-15 19:35:19 03')
) t(dt)
  CROSS APPLY (
                SELECT VALUE
                     , ROW_NUMBER() OVER (ORDER BY VALUE) AS seq
                FROM STRING_SPLIT(t.dt, ' ')
) ch
WHERE
  seq = 1

 --------------------------- ---------------------- 
|offset_val                 |dt                    |
 --------------------------- ---------------------- 
|2015-05-16 00:35:19.0000000|2015-05-15 21:35:19 03|
|2015-05-15 22:35:19.0000000|2015-05-15 19:35:19 03|
 --------------------------- ----------------------

CodePudding user response：

You got the offset the otehr way around, 21:35:19 03 is UTC 3 and it represents 18:35:19 in UTC.

That being said, your string representation parses just fine as datetimeoffset and you can then just convert it to UTC:

SELECT PARSE('2015-05-15 21:35:19 03' AS datetimeoffset USING 'en-us') AT TIME ZONE 'UTC'