Mysql converting varchar column values to datetime

Question

I have table column with datatype as VARCHAR(255) and value in it as "Dec 31 1969 18:00:00". I need to convert this value in date format like Y-m-d H:i:s i.e. 1969-12-31 18:00:00 Tried with the solution using STR_TO_DATE, but is not working anymore. I tried like -

SELECT STR_TO_DATE('Dec 31 1969 18:00:00','%M %d,%Y %h:%i:%s')

BUt, this returns NULL.

Any help is appreciated. Thank you.

Usagi Miyamoto · Accepted Answer · 2020-08-27 06:46:34Z

1

Remove the comma (,) from the format.
%M is the full name of the month, not abbreviated, like %b.
%h is 12-hour hours value. Use %H or use %T for the whole time part.

Try something like this:

SELECT STR_TO_DATE('Dec 31 1969 18:00:00','%b %d %Y %T')

answered Aug 27, 2020 at 6:46

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql converting varchar column values to datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related