mysql date format regarding to a specific country

Question

I used MySQL function DATE_FORMAT(table.field, '%d %b %Y'), where the first parameter is my column, and the second is a pattern to format the column according to a date logic.

The output of this function is in english :

10 Feb 2014

And I would like to set the locale language to fr_FR which would output this correct string :

10 Fév 2014

Question : How to change locale language of the DataBase right before the query (in order to change it according to another language) ?

Economically

Answer 1

you need to set the correct language setting in the MySQL Server. Look here: MySQL DATE_FORMAT() function

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable (Section 10.7, “MySQL Server Locale Support”).

Run this before your query:

SET lc_time_names = 'fr_FR';

Then set it back to the original value of lc_time_names;

Pyrostat

this helped me, solve my further problem in php using mysql multi query, thx 👍 – Loudmouthed

Answer 2

You could simply pass the third parameter as a local value. ^^

SELECT DATE_FORMAT("2023-06-15", '%W %d %M %Y','it_IT') as date;

output: giovedì 15 giugno 2023

SELECT DATE_FORMAT("2023-06-15", '%W %d %M %Y','fr_FR') as date;

output : jeudi 15 juin 2023

SELECT DATE_FORMAT("2023-06-15", '%W %d %M %Y','ja_JP') as date;

output : 木曜日 15 6月 2023

for more information, here MySQL Server Locale Support

Mesocratic

Answer 3

Try this, hope it will help:

SELECT
  CONCAT(DATE_FORMAT(CURDATE(), '%d'), " ",
  CASE
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Jan' THEN '01'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Feb' THEN '02'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Mar' THEN '03'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Apr' THEN '04'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'May' THEN '05'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Jun' THEN '06'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Jul' THEN '07'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Aug' THEN '08'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Sep' THEN '09'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Oct' THEN '10'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Nov' THEN '11'
    WHEN DATE_FORMAT(CURDATE(), '%b') = 'Dec' THEN '12'
  END, " ",
  DATE_FORMAT(CURDATE(), '%Y')) AS DATE

Overpowering

Thank you for you input Zega, but I do not think this solve the problem because it would force a date format this way, and it would also not work for let us say french (because you would have to overwrite the case statement for the language or your choice). I think @Yasen Zhelev answer solve the issue nicely and I will stick with this for the moment thank you. – Economically