Skip to main content
vaishals
vaishals
March 24, 2023
Question

Convert date in text format to date time format

  • March 24, 2023
  • 1 reply
  • 0 views

Hi All,

One of our old application was storing date time as TEXT format. Some of the values in DB for column called 'datetime TEXT" column are 

5/15/2018 2:57 PM GMT-04:00

5/15/2018 2:57 PM GMT-05:00 

3/12/2018 5:25 PM EDT

2/6/2018 1:48 PM EST

Recently we are trying to add new column to store this value. Just say "dateTime" column with datetime type. On doing so we will be storing in datetime format for all new request after this update. But for older request the value will be in TEXT format as mentioned above.

Can anyone suggest best way to convert and populate datetime for older request into the new column which i have created.

dateTime(TEXT) dateTime
5/15/2018 2:57 PM GMT-04:00 ? old
5/15/2018 2:57 PM GMT-04:00 ? old
5/15/2018 2:57 PM GMT-04:00 ? old
10/18/2017 14:02 new
10/18/2017 14:02 new
10/18/2017 14:02 new

1 reply

harshitb6843
harshitb6843
March 24, 2023

Backup the entire table first. Then, try to run the below query on smaller data before you run it on the entire table. 

UPDATE table_name SET dateTime = CONVERT(datetime, dateTime(TEXT), 120);

Terms & ConditionsAccessibility statement