Calculate the year for ending month/day?
- by Dave Jarvis
Given:
Start Year
Start Month & Start Day
End Month & End Day
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
Start Year = 2009
Specific Date = 2010-01-14
TRUE
2nd example:
Start Date = 11-22
End Date = 11-16
Start Year = 2009
Specific Date = 2010-11-20
FALSE
3rd example:
Start Date = 02-25
End Date = 03-19
Start 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 1
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.
Update 2
Here is what I was thinking about for obtaining the end year:
end_year =
case
sign( diff(
date( concat_ws( year, start_month, start_day ) ),
date( concat_ws( year, end_month, end_day ) ) ) )
when -1 then Start_Year + 1
else Start_Year
end case
Then wrap that expression (once syntactically correct) inside of another date, followed by BETWEEN statement.
Update 3
To clear up some confusion: there is no end year. The end year must be calculated.
Thank you!
