Converting string to timestamp format in MySQL

Question

I have a column in my MySQL database which has timestamp data in string format. I want to convert it into timestamp format.

Below is the sample data:

Date
--------------------------------
Fri Dec 14 14:11:43 IST 2018
Fri Dec 14 14:13:20 IST 2018

I'm expecting the result to be in the following format:

14-12-2018 14:11:43
14-12-2018 14:13:20

Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — But those new buttons though.., Dec 28 '18 at 07:14

score 3 · Accepted Answer · answered Dec 28 '18 at 07:09

By using the STR_TO_DATE() to convert the string into a DATETIME, then by using DATE_FORMAT() can change it to the expected date time format.

The following query will return the expected output date time format:

SELECT DATE_FORMAT(
         STR_TO_DATE('Fri Dec 14 14:11:43 IST 2018', '%a %b %d %T IST %Y'), 
       '%d-%m-%Y %H:%i:%s');

Output:

14-12-2018 14:11:43

Reference formats are available in this link

score 0 · Answer 2 · answered Dec 28 '18 at 07:00

0

You could use str_to_date()

str_to_date('Fri Dec 14 14:11:43 IST 2018', '%a %b %d %T IST %Y');

answered Dec 28 '18 at 07:00

ScaisEdge

2 Answers2