CODEWITHSUNDEEP
sqloracle-databaseoracle10g
AT-2017
AT-2017

Reputation: 3149

Find Dates in Range Using Query

I am trying to check or validate dates in a range using Oracle sql and tried something like this:

SELECT * FROM VW_LEAVEDETAILS m WHERE M.EMPNO = '123456'
AND M.LV_FROM >= '17-OCT-2018' AND M.LV_TO <= '18-OCT-2018';


Date Range: Start - End
            20-OCT-2018 - 22-OCT-2018 //This worked when checked

This works fine for a specific date range. But what if I've the following date ranges

Date Range: Start - End
            22-OCT-2018 - 22-OCT-2018 //Date range 1 - in Database Table
            18-OCT-2018 - 22-OCT-2018 //Date range 2 - User input
            22-OCT-2018 - 24-OCT-2018 //Date range 2 - User input; Even this should be validated

See for the above 22-OCT-2018 is already allocated for Database Table date range and whenever one tries to allocate that again, should throw an error or at least pull up the range. I was trying something silly like this, the reverse one but stuck:

SELECT * FROM VW_LEAVEDETAILS m WHERE M.EMPNO = '123456'
AND M.LV_FROM >= '17-OCT-2018' AND M.LV_TO <= '18-OCT-2018'
OR M.LV_TO >= '17-OCT-2018' AND M.FROM <= '18-OCT-2018';

N.B: Basically it's for leave management system. So whenever there is a date allocated within the range, it should validate. There are three conditions below:

LV_FROM is between user start and user end date
LV_TO is between user start and user end date
LV_FROM is before user start date and LV_TO is after user end date

Credit goes to @Corion for the editing suggestion - Thanks.

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you want overlaps, then the logic is:

SELECT m.*
FROM VW_LEAVEDETAILS m
WHERE M.EMPNO = '123456' AND
      M.LV_TO >= DATE '2018-10-17' AND
      M.LV_FROM <= DATE '2018-10-18';

Two periods overlap if one starts before the second ends and the first ends after the second starts (the end points may or may not be included, depending on your overlap logic).

Upvotes: 2

Corion
Corion

Reputation: 3925

It seems to me that you are interested in all the rows that overlap with the range that the user enters.

There can be three types of overlap:

  1. LV_FROM is between user start and user end date.
  2. LV_TO is between user start and user end date.
  3. LV_FROM is before user start date and LV_TO is after user end date.

So, let's turn these into three conditions:

SELECT * FROM VW_LEAVEDETAILS m WHERE M.EMPNO = '123456'
AND (
    (M.LV_FROM between '17-OCT-2018' AND '18-OCT-2018')
 or (M.LV_TO   between '17-OCT-2018' AND '18-OCT-2018')
 or (M.LV_FROM <       '17-OCT-2018' AND M.LV_TO > '18-OCT-2018')
);

If you can provide more information and example data, that can help us provide better answers.

Upvotes: 2

Related Questions