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.

score 6 · Accepted Answer · answered Jun 23 '17 at 07:20

6

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

answered Jun 23 '17 at 07:20

Pரதீப்

91,748
19
131
172

This works! Thank you very much! Do you have any idea if there is something equivalent for postgresql? – Mewtwo Jun 23 '17 at 07:52
@lacrima - Sorry I don't know `Postgresql` – Pரதீப் Jun 23 '17 at 07:53
Thank you -- this is the only working answer I could find to a similar problem. – Tim van der Leeuw Jan 28 '20 at 21:06

adn · Answer 2 · 2017-06-23T08:00:11.437

0

Postgres :

to_timestamp('2017-06-22T00:43:15+0300', 'YYYY-MM-DD HH24:MI:SS')

you can try using that postgres function.

edited Jun 23 '17 at 08:00

answered Jun 23 '17 at 07:17

adn

430
2
7
20

I would like to keep all of the information from the timestamp. This will only keep year, month and day. Actually I would like to end up with exactly the same format but of type datetime, not string – Mewtwo Jun 23 '17 at 07:25
@lacrima, try this to_timestamp('2017-06-22T00:43:15+0300', 'YYYY-MM-DD HH24:MI:SS') that's postgresql function – adn Jun 23 '17 at 07:59
That's what I was doing until now but again I lose the timezone information – Mewtwo Jun 23 '17 at 08:30

Converting string to datetimeoffset in SQL Server and Postgresql

2 Answers2