1

Working in c#. I have two date object named start_date_entered and end_date_entered and I have two columns in my table start_date and end_date. I have to query my table that if any of the dates between start_date_entered and end_date_entered are present in between start_date and end_date.

DateTime start_date_entered = Convert.ToDateTime('01/01/2018 12:00:00 AM');
DateTime end_date_entered = Convert.ToDateTime('03/01/2018 12:00:00 AM');

the database column start_date contains let say 25th march and end_date contains 27th march. If the both sequences have any date common then no row should be returned from the database. If there is any common date between both sequences then database should return the row. 

select * from employee_leaves where ... between start_date and end_date

what should i write in "..." as I want to put a sequence of dates between start_date_entered and end_date_entered here

I have tried these,

Fetching Dates Which Comes Between StartDate and EndDate

How to list all dates between two dates [duplicate]

But haven't find any solution.

Farrukh Sarmad
  • 315
  • 2
  • 5
  • 24

1 Answers1

5

If you're querying for everything that intersects a given time range (start/end) - try visualizing all the possibilities of things you want to include; for example:

       |<--range-->|
       |           |
   [---+-----------+----]          scenario 1 - starts before, ends after
       |   [---]   |               scenario 2 - starts and ends inside
       |   [-------+-----------]   scenario 3 - starts inside, ends after
   [---+-------]   |               scenario 4 - starts before, ends inside
       |           |

Scenarios 2, 3, and 4 will be handled by checking whether either end is in the query; scenario 1 needs to be handled separately; so:

where x.Start between @start and @end
   or x.End between @start and @end
   or (x.Start < @start and x.End > @end)

Note that between is an inclusive range query; if you need more control of the boundary conditions, expand between to an explicit pair of start/end limits with your choice of inequality operators.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • for any of the 4 scenarios, no row should be returned from database. – Farrukh Sarmad Feb 08 '18 at 10:57
  • @FarrukhSarmad fine; I don't understand why not, but whatever - not my problem; you know the rules about which rows **you want** to return - so: draw the equivalent map for the things you want to include and exclude, and from that determine the appropriate `where` filter for your needs; the example here - while being what most people usually want when doing a date-range query over entities with a date-range - was meant to be illustrative, not definitive – Marc Gravell Feb 08 '18 at 11:01
  • there is a leave for an employee in the database that is approved. I want to restrict the user to again request for a leave for the same dates he has already approved. – Farrukh Sarmad Feb 08 '18 at 11:09
  • @FarrukhSarmad great; so draw out the table for what you want, and implement the corresponding `where` query... frankly, it seems to me that the code I've already posted will find any leave scheduled for the range in question, so I'm not sure how the code I've posted isn't a 100% fit. – Marc Gravell Feb 08 '18 at 11:10
  • I modifies it with my vareiables so your code is returning a row if any of the date resides. Now when I read it in the dataReader object if dr.Read() will return true then I will prompt the user that your entered date has already been approved for a leave :) – Farrukh Sarmad Feb 08 '18 at 11:15
  • See also [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/q/325933/18192). You can check scenarios 1-4 in only two comparisons. – Brian Feb 08 '18 at 14:14