Question:
I have a table that records my records where one column writes the data_inicial and another column the data_final .
I need to generate a report where the user searches all records that were between the period of for example 03/01/2017 to 03/31/2017.
What I needed is to get the records that even having started before this period occupied days in my month 03. If I use, for example, data_inicial between 01/03/2017 and 31/03/2017 , my records that had start date 27/ 02/2017 and end date 01/04/2017 do not appear.
How could I do this?
Answer:
The between will not allow it because it is direct: Only records from 03/01/2017 TO 03/31/2017 inclusive will be returned.
To ALSO consider periods that contain month 03, you will have to include the OR clause indicating that you want the smallest and, at the same time, greater than month 03.
All records that at least started before 3/31/03 and ended after.
select * from teste
where dataInicial between '01/03/2017' and '31/03/2017'
or (dataInicial < '01/03/2017' and dataFinal > '31/03/2017')
In this case, it will also consider those who at least finished within month 03
select * from teste
where dataInicial between '01/03/2017' and '31/03/2017'
or (dataInicial < '01/03/2017' and dataFinal >= '01/03/2017')