Mani Mani - 5 months ago 7
SQL Question

Checking if the "system" falls between two dates in SQL

The Scenarion is DEMO001 system is booked from 10-Aug to 11-Aug by some user.

START_DATE END DATE SYSTEM
2016-08-10 2016-08-11 DEMO001


If Other user tries to book this system between these days . We have to check and pass 0 or 1.

I will get an input parameter say start_date as 2016-08-08 and 2016-08-15.

I have to write a PLSQL/SQL code to pass 0 because DEMO001 is falling between these start data and end date of the input else pass 1.

Kindly help.

Answer

Based on your requirements, you want to disallow a booking if either the start or end date falls in between a given record. You can just use a CASE expression for this. I have hardcoded 2016-08-08 and 2016-08-15 as the start and end dates which you are using.

SELECT SYSTEM,
    CASE WHEN (START_DATE >= '2016-08-08' AND START_DATE <= '2016-08-15') OR
              (END_DATE   >= '2016-08-08' AND END_DATE   <= '2016-08-15')
         THEN 0
         ELSE 1
    END AS allowBooking
FROM yourTable
Comments