Search code examples
phpmysqldatetimestr-to-date

How to convert varchar to a date in php mysql?

I have in my database a date fetcha_pago in the format: "08-Abr-2016" and it is a varchar. When I do the following: 

STR_TO_DATE(fecha_pago, '%d-%M-%Y')

It does not work because the date is in Spanish. It has to be "Apr" instead of "Abr". Is there a way to respect "Abr" for the month?

Solution

  • Storing dates as strings causes all sorts of problems and locale is one of them. If you can convert these to ISO-8601 format, YYYY-MM-DD HH:MM:SS in a DATE or DATETIME column.

    Date formatting should be done in PHP and only when you know the locale you're rendering into. Anything in the database should be stored in a neutral format. String dates are borderline useless, they can't be sorted or indexed.

    You'll probably have to chain twelve REPLACE() statements together to re-write each month name:

    STR_TO_DATE(REPLACE(REPLACE(fetcha_pago, 'Ene', 'Jan'), 'Abr', 'Apr'), '%d-%M-%Y')

    That's just two levels, but you can expand to cover all those that are different.