Convert a value(string) to timestamp (Input string has unwanted characters) - SQL

Question

I'm trying to convert a value in my table to a timestamp but the value in the table is as below

There's a character 'T' after the date in the string.

GMT_TIME

20210608T111722.837 GMT

1st Goal: Achieve the result in the below format

Output :

GMT_TIME

2021/06/08 11:17:22:837

2nd Goal: Trying to convert it into EST time

Output

2021/06/08 07:17:22:837

''' select DATE( DATE_SUB( '1st goal output') , INTERVAL 4 HOUR ) ) from table1 '''

Please give me your valuable input on this. Thank you

Answer 1

SELECT CAST(SUBSTRING_INDEX(REPLACE('20210608T111722.837 GMT', 'T', ''), ' ', 1) AS DATETIME(3))

Output: 2021-06-08 11:17:22.837.

For to convert from GMT to EST use CONVERT_TZ() function. Remember - you must import timezones into your server system database for to use mnemonic TZ names (not needed if you use time-formatted offsets).

If you want to substract 4 hours unconditionally then

SELECT SUBSTRING_INDEX(REPLACE('20210608T111722.837 GMT', 'T', ''), ' ', 1) - INTERVAL 4 HOUR

In this case the output datatype will be DATETIME(6), if you need strictly DATETIME(3) then use explicit CAST() additionally.