sql show date between start date and end date

Question

I would like to know how I can compare a birthday date between a start date and end date.

For now I use this:

select *
from users
where dayofyear(birthday) between dayofyear(start_date) and dayofyear(end_date);

the problem is if start_date = 2011-12-01 and end_date = 2012-01-01 then I get no results.

If the user birthday is 1931-12-12 any year, it must be in the query result.

For now i get no result.

someone have a hint to resolve this kind of issue? Thanks.

possible duplicate of [select date](http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates) — Punit, Dec 02 '11 at 04:36
Can you provide more detail on what you are trying to accomplish? Are you trying to get a couple of days advance notice of a birthday? — Gustav Bertram, Dec 02 '11 at 04:55
Thanks for reply. What i want to do, is to show result between a start_date and end_date based on birthday if user birthday are 1931-12-15 and start_date are 2011-12-01 and end_date are 2012-01-01 the user must be in the result. — neimad, Dec 02 '11 at 05:33

score 2 · Accepted Answer · answered Dec 02 '11 at 05:35

The following is a little ugly but it accounts for all birthdays that occur in the time span between start_date and end_date. If the time span is greater than a year then it basically returns everyone because everyone has a birthday somewhere in that time span. If it is less than a year but start and end are on different years it uses two between clauses to capture both the start time to the end of the year and the start of the year to the end time. This query also assumes that start_date <= end_date.

As long as you have an index on DAYOFYEAR( birthday ) this query is performant despite it's appearance.

SET @start_date = '2011-12-01';
SET @end_date = '2012-01-01';

SELECT * FROM users
WHERE DAYOFYEAR( birthday )
  BETWEEN IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 1,
    IF( YEAR( @end_date ) - YEAR( @start_date ) > 0,
      IF( DAYOFYEAR( @start_date ) <= DAYOFYEAR( @end_date ), 1,
        DAYOFYEAR( @start_date ) ),
      DAYOFYEAR( @start_date ) ) )
      AND IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 366,
            IF( YEAR( @end_date ) - YEAR( @start_date ) > 0, 366,
              DAYOFYEAR( @end_date ) ) )
  OR DAYOFYEAR( birthday )
  BETWEEN IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 1,
    IF( YEAR( @end_date ) - YEAR( @start_date ) > 0, 1,
      DAYOFYEAR( @start_date ) ) )
         AND IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 366,
               IF( YEAR( @end_date ) - YEAR( @start_date ) > 0,
                 IF( DAYOFYEAR( @start_date ) <= DAYOFYEAR( @end_date ), 366,
                   DAYOFYEAR( @end_date ) ),
                 DAYOFYEAR( @end_date ) ) );

Wow, thanks a lot, i never expected we can use condition like that in a sql query. — neimad, Dec 02 '11 at 05:42

sql show date between start date and end date

1 Answers1