Date between dates ignoring year

Question

What is the best (fastest) approach to compare if the date is in the date range regardless of the year?

date table:

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

Now I want this request to return 'foo'

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

If necessary, I can change the type / format of the stored dates in the "dates" table, but I cannot change the date format that I entered in the query, since this value usually comes from another table (this is part of a more complex query using joins).

Answer 1

Use the DayOfYear function:

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

Answer 2

 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 is the SQL FIDDLE demo

Answer 3

Since you indicated that your data is stored for an arbitrary year, you can continue to use the DATE type. Then it comes down to how to request.

• Save the fields using a fixed arbitrary year. You must use the leap year to post opportunities on February 29th. Year 2000 works beautifully. (0004 will not work because it is too small for the MySQL DATE type.)

• Replace the year of any value you request with the same year.

• Consider the ranges that intersect at the end of one year and the next. To fully answer this question, you will need the following query:

   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 script that demonstrates

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

Answer 4

After a brief look at Google and using stackoverlook, I came across the following two problems:

• in one year, 1 day more (February 29)
• The requested range ( since and upto ) may refer to different years, so using DayOfYear () is a problem. This is the SQL query that I came up with.

Let's say we have the 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" ) ) ) )