How to properly display data from a commemorative date table

Navigation
0

Table entries

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

Example: I want to bring the commemorative dates that are between January 15 and February 15, but when the filter is from one month to the other no results are returned, but if I enter with the filter being dates from 01 to 15 of January, the query returns me 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
    
asked by anonymous 24.01.2018 / 21:10

2 answers

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 anyway ...

I tested it here: link

    
25.01.2018 / 13:36
1

You are using AND in all conditions, but you actually need to filter if the day is greater than 15 only when the month is 01, and less 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
    
24.01.2018 / 21:17
Test the same as the question but is giving error In pl-sql can I assign the SUM value to a variable?