How to convert UTC datetime values stored as a VARCHAR to a regular datetime

Question

I have this date '2021-04-22T17:06:59.391Z' stored in UTC datetime, but the column datatype is VARCHAR. How do I convert this to a regular datetime with column datatype date ?

Existing column (type VARCHAR) - 2021-04-22T17:06:59.391Z
Expected output (type DATE) - 2021-04-22 17:06:59

Does this answer your question? [Convert Datetime column from UTC to local time in select statement](https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement) — Brad, Apr 22 '21 at 19:29
@jarlh I am using SQL Workbench/J connected to a redshift cluster. — Abhijeet Deshmukh, Apr 22 '21 at 20:04
I'm a little confused. Dates in Redshift don't have a time component. — Gordon Linoff, Apr 22 '21 at 21:38

score 0 · Answer 1 · answered Apr 22 '21 at 20:07

0

You can use this to get the expected result.

SELECT CONVERT(datetime2(0), CONVERT(DATETIMEOFFSET,'2021-04-22T17:06:59.391Z'))

answered Apr 22 '21 at 20:07

Samim Hussain

396
5
14

Could you please share the error screen ? – Samim Hussain Apr 22 '21 at 21:20
Invalid operation: syntax error at end of input Position: 8; [SQL State=42601, DB Errorcode=500310] 1 statement failed. – Abhijeet Deshmukh Apr 22 '21 at 21:53

How to convert UTC datetime values stored as a VARCHAR to a regular datetime

1 Answers1