1

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?

Suever
  • 61,031
  • 14
  • 77
  • 92
Isaac Hernández
  • 77
  • 1
  • 9

1 Answers1

0

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.

tadman
  • 194,930
  • 21
  • 217
  • 240
  • This solution works form me : STR_TO_DATE(REPLACE(REPLACE(fetcha_pago, 'Ene', 'Jan'), 'Abr', 'Apr'), '%d-%M-%Y'), But do i need to replace all the months ? or just the ones that change?? – Isaac Hernández Apr 19 '16 at 17:57
  • Just the ones that change. If there are some that are coincidentally the same you can skip those. The expanded version is pretty huge, so I left that out, but just repeat that pattern. – tadman Apr 19 '16 at 18:21
  • Thank you. You help me a lot =) – Isaac Hernández Apr 19 '16 at 18:31