Calculate the year for ending month/day?

Question

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? The problem is in finding the end year so as to get a start_date and an end_date.

Answer 1

Maybe convert the dates to UNIX timestamps? Then it would be simple less-than or greater-than integer arithmetic.

Answer 2

Do you really need true/false values, or can you just SELECT? Assuming $date contains the date you're looking for in 'yyyy-mm-dd' format:

SELECT * FROM mytable WHERE $date > start_date AND date < end_date;

Having the year in a separate field also works, but looks uglier and probably kills performance:

SELECT * FROM mytable WHERE $date > CONCAT(year, '-', start_date) AND
  $date < CONCAT(year, '-', end_date);

And with handling for the end < start case:

SELECT * FROM mytable WHERE $date > CONCAT(year, '-', start_date) AND $date <
  IF(start_date < end_date,
     CONCAT(year, '-', end_date),
     DATE_ADD(CONCAT(year, '-', end_date), INTERVAL 1 YEAR));

Answer 3

One way to calculate the end year (so as to derive the end_date):

end_year = start_year +
  greatest( -1 *
    sign(
      datediff(
        date(
          concat_ws('-', year, end_month, end_day )
        ),
        date(
          concat_ws('-', year, start_month, start_day )
        )
      )
    ), 0
  )