SQL/ORACLE Filter active records in the month

Asked

Viewed 467 times

3

I have a table that records my records where a column records the data_inicial and another column to data_final.

I need to generate a report where the user searches all records that were between the period for example 01/03/2017 to 31/03/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 initial date 27/02/2017 and final date 01/04/2017 do not appear.

How could I do that?

sql query oracle date

  • Hello Matheus, consider accepting my answer if it has been useful to you. If you think she’s incomplete or doesn’t respond to you, make the appropriate comments so I can improve her.

1 answer

2

The between will not allow because it is direct: Only the records of the day 01/03/2017 UNTIL 31/03/2017 inclusive will be returned.

To consider ALSO the periods containing the month 03, will have to include the clause OR indicating that either the minors and at the same time, larger than the month 03.

All records that at least started before 31/03 and that 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, you will also consider those who have at least finished within the 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')

Browser other questions tagged sql query oracle date

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