Select with the day of the week in Portuguese

Question

Good afternoon Guys, I need to make a select that translates the day of the week into Portuguese, it is already working, but in English. How can I change.

Select *,id, data, mes,date_format(`data`,'%d/%m/%Y') as `data_formatada` FROM (SELECT DAYNAME(data) AS dia, year(data) AS ano,    
                    (CASE month(data) 
                       when 1 then 'Janeiro'
                       when 2 then 'Fevereiro'
                       when 3 then 'Março'
                       when 4 then 'Abril'
                       when 5 then 'Maio'
                       when 6 then 'Junho'
                       when 7 then 'Julho'
                       when 8 then 'Agosto'
                       when 9 then 'Setembro'
                       when 10 then 'Outubro'
                       when 11 then 'Novembro'
                       when 12 then 'Dezembro'
                       END) AS mes,
                       id, 
                       data,hora,evento,participante FROM agenda WHERE not (data is null)) as agenda

Thank you.

Answer 1

Just use lc_time_names = 'pt_PT';

SET lc_time_names = 'pt_PT';

Select  DAYNAME(NOW()) AS dia, year(NOW()) AS ano,    
    (CASE month(NOW()) 
       when 1 then 'Janeiro'
       when 2 then 'Fevereiro'
       when 3 then 'Março'
       when 4 then 'Abril'
       when 5 then 'Maio'
       when 6 then 'Junho'
       when 7 then 'Julho'
       when 8 then 'Agosto'
       when 9 then 'Setembro'
       when 10 then 'Outubro'
       when 11 then 'Novembro'
       when 12 then 'Dezembro'
       END) AS mes

Summing up you just really need.

SET lc_time_names = 'pt_PT';

Select  DAYNAME(NOW()) AS dia, year(NOW()) AS ano,     MONTHNAME(NOW()) AS mes

Much simpler.

Answer 2

You can use the function weekday(date) to know the day of the week and the solution will be similar to what you did for the month .

She considers the day of the week as a whole of 0 - 6 starting on Monday and going until Sunday.

Follows a solution:

Select *,id, data, mes,date_format(`data`,'%d/%m/%Y') as `data_formatada` FROM (SELECT DAYNAME(data) AS dia, year(data) AS ano,    
                    (CASE month(data) 
                       when 1 then 'Janeiro'
                       when 2 then 'Fevereiro'
                       when 3 then 'Março'
                       when 4 then 'Abril'
                       when 5 then 'Maio'
                       when 6 then 'Junho'
                       when 7 then 'Julho'
                       when 8 then 'Agosto'
                       when 9 then 'Setembro'
                       when 10 then 'Outubro'
                       when 11 then 'Novembro'
                       when 12 then 'Dezembro'
                       END) AS mes,
 (CASE WEEKDAY(data) 
                       when 0 then 'Segunda-feira'
                       when 1 then 'Terça-feira'
                       when 2 then 'Quarta-feira'
                       when 3 then 'Quinta-feira'
                       when 4 then 'Sexta-feira'
                       when 5 then 'Sábado'
                       when 6 then 'Domingo'                 
                       END) AS DiaDaSemana,
                       id, 
                       data,hora,evento,participante FROM agenda WHERE not (data is null)) as agenda