Most efficient way to check if a date falls between two dates?
- by Dave Jarvis
Given:
Start Month & Start Day
End Month & End Day
Any Year
What SQL statement results in TRUE if a date lands between the Start and End days?
1st example:
Start Date = 11-22
End Date = 01-17
Year = 2009
Specific Date = 2010-01-14
TRUE
2nd example:
Start Date = 11-22
End Date = 11-16
Year = 2009
Specific Date = 2010-11-20
FALSE
3rd example:
Start Date = 02-25
End Date = 03-19
Year = 2004
Specific Date = 2004-02-29
TRUE
I was thinking of using the MySQL functions datediff and sign plus a CASE condition to determine whether the year wraps, but it seems rather expensive. Am looking for a simple, efficient calculation.
Update
The problem is the end date cannot simply use the year. The year must be increased if the end month/day combination happens before the start date. The start date is easy:
Start Date = date( concat_ws( '-', year, Start Month, Start Day )
The end date is not so simple.
Thank you!
