How to correctly display data from a commemorative date table

Asked

Viewed 39 times

0

Sections of the table

The intention is to bring the records of the commemorative dates in an interval of month and day.

Example: I want to bring the commemorative dates that are between 15 January and 15 February, but when the filter is from one month to the other no results are returned, but if I enter the filter being the dates from 01 to 15 January, the query returns all satisfactory results

+-----+-----+--------------------------------------------------+ | Mes | Dia | Descricao | +-----+-----+--------------------------------------------------+ | 01 | 01 | Confraternização Universal | | 01 | 01 | Dia Mundial da Paz | | 01 | 02 | Dia da Abreugrafia | | 01 | 05 | Criação da 1ª Tipografia no Brasil | | 01 | 06 | Dia de Reis | | 01 | 06 | Dia da Gratidão | | 01 | 07 | Dia da Liberdade de Cultos | | 02 | 01 | Dia do Publicitário | | 02 | 02 | Dia do Agente Fiscal | | 02 | 02 | Dia de Iemanjá | | 02 | 05 | Dia do Datiloscopista | | 02 | 07 | Dia do Gráfico | | 02 | 08 | Carnaval | | 02 | 09 | Cinzas | | 02 | 09 | Dia do Zelador | +-----+-----+--------------------------------------------------+

Query

SELECT  
   Datas_comemorativas.Mes AS Mes,  
   Datas_comemorativas.Dia AS Dia,  
   Datas_comemorativas.Evento AS Evento
FROM Datas_comemorativas 
WHERE Dia >= '15'
    AND Mes >= '01'
    AND Dia <= '15'
    AND Mes <= '02'
ORDER BY 
Mes ASC,    
Dia ASC
sql

2 answers

1

You are using AND in all conditions, but in fact you need to filter if the day is longer than 15 only when the month is 01, and smaller than 15 only when the month is 02. Try the following query:

SELECT  
   Datas_comemorativas.Mes AS Mes,  
   Datas_comemorativas.Dia AS Dia,  
   Datas_comemorativas.Evento AS Evento
FROM Datas_comemorativas 
WHERE (Dia >= '15' AND Mes = '01')
   OR (Dia <= '15' AND Mes = '02')
ORDER BY 
Mes ASC,    
Dia ASC

Another alternative using string concatenation:

SELECT  
   Datas_comemorativas.Mes AS Mes,  
   Datas_comemorativas.Dia AS Dia,  
   Datas_comemorativas.Evento AS Evento
FROM Datas_comemorativas 
WHERE (Mes||Dia) between '0115' and '0215' --filtro no formato 'mmdd'
ORDER BY
Mes ASC,
Dia ASC

  • Ai when it is from January 22nd to 28th she brings the whole month

  • @Fabricio In this case, where there is only 1 month, you would have to filter everything with and even. You need a dynamic query where these 2 values are entered by the user?

  • Yes are typed by the user

  • I added a new alternative, using string concatenation (since its field is not of the date type). Take a look if it solves your problem

  • Nope, I’m almost doing a dynamic query when it’s the same month I use and when it’s a different month I use or, it’s a lot of Gambi

1


See if this helps. 

select * from (

select cast('2018/' + cast(mes as varchar) + '/' + cast(dia as varchar) as date) as [data], descricao from @table

) Q

where [data] between '2018-01-15' and '2018-02-15'

I used 2018 to form the date but can use any year, just to make the filter work even...

I tested it here: http://sqlfiddle.com/#! 18/68b42/2

Browser other questions tagged sql

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