CODEWITHSUNDEEP
mysql
Matthew Shine
Matthew Shine

Reputation: 463

SQL Birthday Query

Hey I'm a bit of a beginner with SQL but i'm trying to write a query to pull out all records if its someones birthday within the next 7 days and have looked at other threads with answers but I'm having trouble adapting them to my setup.

    SELECT *
    FROM `QG04c`
    WHERE month( `dob` ) = month( now( ) )
    AND day( `dob` )
    BETWEEN day( now( ) )
    AND day( now( ) ) +7
    AND `Primary Unit?` =1

At the moment this pulls out everyone whose birthday is within 7 days but I don't think it will cope with end of the month scenarios and end of year etc.

Upvotes: 0

Views: 1370

Answers (3)

GarethD
GarethD

Reputation: 69749

Assuming this is MySQL, you can use the DAYOFYEAR function to get the day of the year a birthday occurs. The simple scenario is everything from 1st Jan - 25th December, where 7 days ahead is in the same year. For this you can use:

SELECT  *
FROM    `QG04c`
WHERE   DAYOFYEAR(DOB) - DAYOFYEAR(CURDATE()) BETWEEN 0 AND 7;

However when today is between 25th Dec - 31st Dec you need to account for the birthday being between 1st and 6th Jan. To do this you need:

SELECT  *
FROM    `QG04c`
WHERE   DAYOFYEAR(CURDATE() + INTERVAL 7 DAY) < 7
AND     DAYOFYEAR(DOB) < DAYOFYEAR(CURDATE() + INTERVAL 7 DAY);

Then it is just a matter of combining the two cases:

SELECT  *
FROM    `QG04c`
WHERE   DAYOFYEAR(DOB) - DAYOFYEAR(CURDATE()) BETWEEN 0 AND 7
OR  (   DAYOFYEAR(CURDATE() + INTERVAL 7 DAY) < 7
    AND DAYOFYEAR(DOB) < DAYOFYEAR(CURDATE() + INTERVAL 7 DAY)
    );

Examples on SQL Fiddle

Upvotes: 2

Andomar
Andomar

Reputation: 238048

Should be easy if you don't decompose the day?

WHERE `dob` BETWEEN now() AND now() +7

Upvotes: 0

Adalarasan_Serangulam
Adalarasan_Serangulam

Reputation: 728

select * from tablename WHERE [ColumnName] BETWEEN DATEADD(DAY, +7, @YourDate) AND @YourDate and Primary Unit = 1 (If you have and condition put add here)

Thanks .

Upvotes: 0

Related Questions