Check if two dates completely overlaps a date range

Question

I have a parking permit table with validFrom and validTo fields which are datetime fields. I also have another table called OvernightParkingSetting which has two datetime fields NightStart and NightEnd.

What I need is to read all of the parking permits that are valid beginning before NightStart and ending after NightEnd, meaning it is a pass valid for overnight parking.

I'm trying to do this in .NET, LINQ

Any help with the logic or the design would be appreciated.

I don't understand your question. What does that have to do with the validfrom and validto fields? And what do you mean by completely cross NightStart and NightEnd? — C.J., Jul 23 '14 at 15:25
Check this SO question [This SO Question][1] [1]: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964 — Charles Bretana, Jul 23 '14 at 15:25

score 0 · Answer 1 · answered Jul 23 '14 at 15:28

I'm not sure how to write the whole code for your project, but I can help you in the right direction with the logic and SQL statement. You'll need to use a couple subqueries to get the values from the OvernightParkingSetting table that you need.

Start here:

SELECT * FROM parkingPermitTable
WHERE validFrom < SELECT(NightStart FROM OvernightParkingSetting) 
    AND validTo > SELECT(NightEnd FROM OvernightParkingSetting)

Here I'm making the assumption that your OvernightParkingSetting table has only one row. If it does not, you need to find a way to restrict that to receiving a specific night start and night end time.

This will select any rows from the parking permit table that has a valid from time earlier than the night start time, as well as a valid to time that is later than the night end time. If you want this to be inclusive, you can change the less than and greater than operators accordingly.

EDIT

This also makes the assumptions that all of the variables are datetime variables. I believe that is the variable that would be most relevant here.

Were you ever able to solve this problem? Just curious what your solution was. — AdamMc331, Jul 24 '14 at 16:49

Check if two dates completely overlaps a date range

1 Answers1