Search code examples
mysqldatetimetimezonestr-to-date

Convert String long date to datetime in MySQL

I get this value 'Mon Oct 26 16:34:23 CST 2015' from a table as a String.

I want to convert this string to the next datetime value '2015-10-26 16:34:23' using str_to_date(), as a follow:

select str_to_date('Mon Oct 26 16:34:23 CST 2015','%a %b %d %H:%i:%s %Y') val;

But it returns me a null value, if I remove '%Y', I get the next: '0000-10-26 16:34:23'.

How I can skip 'CST' from the String to get the year value?

Use replace is the immediate solution, but I can not discard other time zone formats.

In advance, thanks.

Solution

  • You can include the timezone itself in the format string. So, this works:

    select str_to_date('Mon Oct 26 16:34:23 CST 2015','%a %b %d %H:%i:%s CST %Y') val;

    Of course, this does not work if your strings have other timezones.