Date between dates, ignore year

Question

What's the best (fastest) approach to compare if date is in range of dates independently from year?

table "dates":

    some_column| since        | upto        |  
    -----------|--------------|-------------|
   'foo'       | '2011-05-01' | '2013-06-01'|

Now I want this query to return 'foo'

SELECT foo FROM dates WHERE '2014-05-05' BETWEEN `since` AND `upto`

If needed, I can change type/format of stored dates in "dates" table, but I cannot change format of date which I put into query as that value is typically from another table (It's part of more complex query using joins).

Answer 1

SELECT foo FROM dates WHERE DATE_FORMAT('2014-01-15', "%m-%d") 
    BETWEEN DATE_FORMAT(`since`,"%m-%d") AND DATE_FORMAT(`upto`,"%m-%d")

Here's the SQL FIDDLE demo

Answer 2

Use the DayOfYear function:

 SELECT foo FROM dates WHERE DayOfYear('2014-05-05') 
    BETWEEN DayOfYear(`since`) AND DayOfYear(`upto`)

Answer 3

Since you indicated that you're fine storing an arbitrary year in your data, then you can keep using a DATE type. Then it just comes down to how to query.

• Store the fields using a fixed arbitrary year. You should use a leap-year to accommodate the possibility of Feb 29th values. Year 2000 works nicely. (0004 won't work because it's too small for MySQL's Date type.)

• Replace the year of any value you're querying with the same year.

• Consider ranges that cross over the end of one year and into the next. To fully answer that, you'll need a query such as the following:

  SET @dt = cast(CONCAT('2000',RIGHT(thesourcedatevalue,6)) as DATE);
  
  SELECT some_column FROM dates
  WHERE (since <= upto AND since <= @dt AND upto >= @dt) OR
        (since > upto AND (since <= @dt OR upto >= @dt))
  

  Here is a SQL Fiddle that demonstrates

• For performance, you should be sure that there is an index that includes the since and upto fields.

Answer 4

After a brief google-look and stackoverlook, I came across theses 2 problems :

• some year has 1 day more (29th of feb)
• the range asked (since and upto) can belong to different years, making use of DayOfYear() a problem. This is the sql query I came up with.

Let's say we have a date '2014-05-05' to check :

SELECT foo FROM `dates` WHERE
        (
    /*     --------    since and upto are from same year    --------    */
            YEAR(`since`) = YEAR(`date_fin`)
            AND (
                (
                    MONTH(`since`) < "05"
                    OR
                    (
                        MONTH(`since`) = "05"
                        AND
                        DAY(`since`) <= "05"
                    )
                ) AND (
                    MONTH(`date_fin`) < "05"
                    OR
                    (
                        MONTH(`date_fin`) = "05"
                        AND
                        DAY(`date_fin`) >= "05"
                    )
                )
            )
        )OR(
    /*     --------    since and upto are from different year    --------    */
            YEAR(`since`) <> YEAR(`date_fin`) AND (
                (
                    MONTH(`since`) < "05"
                    OR
                    (
                        MONTH(`since`) = "05"
                        AND
                        DAY(`since`) <= "05"
                    )
                ) OR (
                    MONTH(`date_fin`) > "05"
                    OR
                    (
                        MONTH(`date_fin`) = "05"
                        AND
                        DAY(`date_fin`) >= "05"
                    )
                )
            )
        )

Answer 5

I solved a similar problem in the following way:

SELECT foo FROM dates

WHERE   '0000-05-05'
BETWEEN SUBDATE(since, INTERVAL YEAR(since) YEAR)
AND     SUBDATE(upto, INTERVAL YEAR(since) YEAR)

OR      ADDDATE('0000-05-05', INTERVAL 1 YEAR)
BETWEEN SUBDATE(since, INTERVAL YEAR(since) YEAR)
AND     SUBDATE(upto, INTERVAL YEAR(since) YEAR)

First, we are resetting the all dates to zeroes, next we are looking for specific date (also resetting to zero) in between two ranges. In behind example we passed already "resetting" date from external source, but this also can be done in mysql:

WHERE   SUBDATE('2014-05-05', INTERVAL YEAR('2014-05-05') YEAR)