Mysql Ireport DATE_FORMAT months in English

Question

I have some graphics using IReport, in one of them needed to show the date in "month/year" format. Using the code:

DATE_FORMAT(data,'%m/%y') as mesAno

can only show in English format (as image below), but precise in "pt BR" format for example: Aug/2015, May/2015.

I didn’t want to touch the Mysql settings for this.

Chart example:

This code snippet makes it possible to display month names in "en BR".

 CASE MONTHNAME(data) "
                    + "when 'January' then 'Janeiro'\n"
                    + "when 'February' then 'Fevereiro'\n"
                    + "when 'March' then 'Março'\n"
                    + "when 'April' then 'Abril'\n"
                    + "when 'May' then 'Maio'\n"
                    + "when 'June' then 'Junho'\n"
                    + "when 'July' then 'Julho'\n"
                    + "when 'August' then 'Agosto'\n"
                    + "when 'September' then 'Setembro'\n"
                    + "when 'November' then 'Novembro'\n"
                    + "when 'December' then 'Dezembro'"
                    + " END as mes

It would have something like this for the month/year format?

I found on some websites this code: SET lc_time_names = "pt_br" ; But I couldn’t find a way to apply mine query.

If anyone can help, thank you.

Answer 1

You can use the idea you’ve gone through, coupled with the year, using CONCAT. Example:

SELECT CASE MONTHNAME(data) "
                + "when 'January' then 'Janeiro'\n"
                + "when 'February' then 'Fevereiro'\n"
                + "when 'March' then 'Março'\n"
                + "when 'April' then 'Abril'\n"
                + "when 'May' then 'Maio'\n"
                + "when 'June' then 'Junho'\n"
                + "when 'July' then 'Julho'\n"
                + "when 'August' then 'Agosto'\n"
                + "when 'September' then 'Setembro'\n"
                + "when 'November' then 'Novembro'\n"
                + "when 'December' then 'Dezembro'"
                + " END as mes, 
DATE_FORMAT(data,'%y') as ano,
CONCAT(mes, '/', ano) as mesAno
FROM suatabela

That is, you take the fields you defined before, separately, and join them with CONCAT.

Another leaner way to translate the months via mysql SELECT would be like this (after local tests, the function should be inside CONCAT, not understood as extra field):

SELECT CONCAT(ELT(MONTH(data), 'Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez'), '/', YEAR(data)) as mesAno FROM suatabela GROUP BY YEAR(data), MONTH(data) ORDER BY data

Answer 2

Set the locale, you may need to install the en-BR version

setlocale(LC_TIME,"portuguese"); 
DATE('%m/%y') 

Answer 3

You can see the current value of the variable using the Mysql SELECT command:

SELECT @@lc_time_names;

+-----------------+
| @@lc_time_names |
+-----------------+
| pt_BR           |
+-----------------+
1 row in set (0.00 sec)

and use the SET command to change the variable:

SET lc_time_names = 'pt_BR';

Now, take the test:

SELECT DATE_FORMAT('2020-08-06', '%d de %M de %Y');