Mysql Ireport DATE_FORMAT months in English

Asked

Viewed 2,016 times

3

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: inserir a descrição da imagem aqui

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.

mysql ireport

3 answers

2


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

  • I even thought of something in this sense, but, I think it would bring complications at the time of setting the category of the graph: .setCategory(mesAno), I don’t know how I would do it. And also GROUP BY mesAno. What do you think?

  • Category is not about axis. You can have several categories on a chart within the same axis.

  • Regarding group by, you will directly use the database field: GROUP BY YEAR(data), MONTH(data) ... or ... GROUP BY date_format(data, '%Y-%m')

  • I’ll check here, and put the results.

  • I will edit my answer above to show one more way, which is leaner...

  • @ Marcelo Gomes Your second option worked correctly! A presented error unknown field mes in field list

  • Let’s go continue this discussion in chat.

Show 2 more comments

0

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

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

  • @ Saimita I tried very hard to use the LC_TIME , but I couldn’t, in the case of your suggestion, where to put this code: setlocale(LC_TIME,"portuguese");?

0

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');

Browser other questions tagged mysql ireport

You are not signed in. Login or sign up in order to post.