Converting string to datetimeoffset in SQL Server and Postgresql

Question

In SQL server I have a column that stores dates of this format:

2017-06-22T00:43:15+0300

as strings.

What I'm trying to do is cast these strings into actual datatimeoffset data in order to handle them as dates.

So far I have found multiple function for converting from datetimeoffset to other timestamp formats but none of these refer to strings.
https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql

Exactly the same problem arises on a postgresql database. The best I have managed to achieve so far is just truncate the timezone and convert the rest of the string into timestamp. Ideally, I would like not to use such a lossy transformation.

Answer 1

Time Zone Offset in Sql Server is represented like this

[+|-] hh:mm:

In your data semicolon is missing in the offset part

DECLARE @stringtime VARCHAR(100)= '2017-06-22T00:43:15+0300'

SET @stringtime = Reverse(Stuff(Reverse(@stringtime), 3, 0, ':'))

SELECT Cast(@stringtime AS DATETIMEOFFSET(4)) 

Result : 2017-06-22 00:43:15.0000 +03:00