tags:

views:

110

answers:

3

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.

A: 

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

ilikeorangutans
MySQL can compare DATETIME types with all the usual comparison operators quite fine.
Matti Virkkunen
A: 

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));
jpatokal
For the end>start case, DATE_ADD(CONCAT(year, '-', end_date), INTERVAL 1 YEAR) should do the trick.
jpatokal
If you want to do this in SQL, you'll need to cram a CASE or IF in there.
jpatokal
See above for a oneliner.
jpatokal
@jpatokal: I don't think a `CASE` or `IF` is absolutely required.
Dave Jarvis
A: 

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
  )
Dave Jarvis
And the advantage of doing this instead of a simple IF statement is...?
jpatokal
@jpatokal: This actually calculates the correct year for the `end_date`. Your answer *uses* an `end_date` but doesn't show how to calculate its year.
Dave Jarvis
Your question -- which you've rewritten five times now -- asks how to determine whether a date falls between start and end, not how to calculate the end year.
jpatokal
@jpatokal: The question has always been: "Given a start month, start day, end month, end day, and a year: how do you calculate whether a given day falls between the start and end?" The problem is calculating the end year and then convert that to a date. It was a difficult question for me to phrase correctly; apologies for the confusion.
Dave Jarvis
Your question is still whether (= true or false) the date falls between the start and end. To answer this, you do _not_ need to determine the end year separately, it's enough to compute the correct end date and use that on the fly to check -- drum roll -- whether the date falls between the start and the end.
jpatokal