CODEWITHSUNDEEP
mysqldate
0x56794E
0x56794E

Reputation: 21281

Select data between two dates in mysql regardless of year

I need to select all entries between two given dates (Nov 10 and Jan 25, for example) regardless of year.

It's MUCH easier if it would just be Jan 25 and Nov 10. But how would I structure my query to look for all data between Nov 10 and Jan 25?

Upvotes: 1

Views: 1235

Answers (2)

eggyal
eggyal

Reputation: 125835

You can use MySQL's DAYOFYEAR() function:

DAYOFYEAR(field) BETWEEN DAYOFYEAR(A) AND DAYOFYEAR(B)

If DAYOFYEAR(A)>DAYOFYEAR(B) then your application need either replace BETWEEN with NOT BETWEEN or else swap the order of the arguments. If you need to do it in SQL:

DAYOFYEAR(field) BETWEEN
   LEAST(DAYOFYEAR(A), DAYOFYEAR(B)) AND GREATEST(DAYOFYEAR(A), DAYOFYEAR(B))

Be careful of leap years as the DAYOFYEAR() function will take those into consideration

Upvotes: 4

geertjanvdk
geertjanvdk

Reputation: 3520

If you have the exact strings '11 Nov' and '25 Jan', not using any session variables and such:

SELECT * FROM t1 
    WHERE c1 BETWEEN 
      DATE(REPLACE(STR_TO_DATE('11 Nov','%d %b'),'0000',YEAR(NOW()))) AND 
      IF( DATE(REPLACE(STR_TO_DATE('25 Jan','%d %b'),'0000',YEAR(NOW())))
          < DATE(REPLACE(STR_TO_DATE('11 Nov','%d %b'),'0000',YEAR(NOW()))),
          DATE(REPLACE(STR_TO_DATE('25 Jan','%d %b'),'0000',YEAR(NOW())+1)),
          DATE(REPLACE(STR_TO_DATE('25 Jan','%d %b'),'0000',YEAR(NOW()))));

The above is tested, and crazy.

Upvotes: 0

Related Questions