Help With Birthday Dates

[Alechko]

Hello folks,

I have a little problem with dates.

 

I have stored in my SQL table a 'date' type column as `birthday`.

 

Something like that: (YYYY-MM-DD)

2010-05-10

2008-08-14

2002-03-04

2001-04-19

 

Now I'm trying to show the birthdays that will occur more 3 days/this week/this month.

 

I know how to display the specific date by:

$month = '10';
$day = '22';
$sql = $db->Query("SELECT `id` FROM `users` WHERE DATE_FORMAT(`birthday`,'%m') = '{$month}' AND DATE_FORMAT(`birthday`,'%d') = '{$day}'") or die(mysql_error());

 

I am aware of the function strtotime. But I need to show all the birthdays that will occur more 3 days including the tomorrow and the day after tomorrow dates.. and I'm a little bit get complex with it.

 

 

Thanks for the help,

appriciate it.

Edited September 25, 2012 by Alechko

[Jessica]

The phrase "occur more 3 days" doesn't make any sense.

 

Whatever you're trying to do, you'll probably use BETWEEN and/or DATE_SUB() or DATE_ADD()

[silkfire]

So you want to retrieve all users whose birthday is within 3 days? Or within 3 weeks? Or months?

 

Or retrieve all users that together equal at least 3 and have their birthday this month? Or this week?

 

Kinda confused here.

[Barand]

Here are some samples for you

 

Within next 3 days :

...WHERE birthdate BETWEEN CURDATE() AND CURDATE + INTERVAL 3 DAY

 

This month:

...WHERE MONTH(birthdate) = MONTH(CURDATE())

[Alechko]

The phrase "occur more 3 days" doesn't make any sense.

 

Whatever you're trying to do, you'll probably use BETWEEN and/or DATE_SUB() or DATE_ADD()

 

 

So you want to retrieve all users whose birthday is within 3 days? Or within 3 weeks? Or months?

 

Or retrieve all users that together equal at least 3 and have their birthday this month? Or this week?

 

Kinda confused here.

 

Sorry for the English mistakes, I ment within.. For example:

A - 27/09/1980

B - 28/09/1981

C - 29/09/1981

D - 30/09/1981

E - 03/10/1990

 

Today is the 26th of September. I want to display the users who have a birthday in the next three days. So It will display me: A, B, C.

For the whole month it display me all septembers birthday. For the next week it will show me all the letters.

 

Thanks Baranad,

The within next 3 days query dosen't worked for me. It didn't show anything. I think I know why, Because I need to compare only the months and days and not the years.

 

Something like that:

WHERE `birthdate` BETWEEN [ DAY AND MONTH(`birthday`) ] AND [DAY AND MONTH(`birthday`) ] + INTERVAL 3 DAY

 

I don't know what is the exactly commands for that...

 

thanks for help!!

 

p.s

The 'this month' query worked fine for me.

 
SELECT id FROM `users` WHERE MONTH(`birthday`) = MONTH(CURDATE())

[ManiacDan]

Unfortunately, these solutions won't work because my girlfriend's birthday is TODAY, but her birthdate is not within 3 days of today, it was 20 years ago. You need to combine the date_format functions and the str_to_date function to change their birthdates from the year of their birth to this year, something like:

 

WHERE STR_TO_DATE(CONCAT_WS('-', DATE_FORMAT(NOW(), '%Y'), DATE_FORMAT(birthdate, '%M'), DATE_FORMAT(birthdate, '%D')) BETWEEN DATE_SUB(NOW(), INTERVAL 3 DAY) AND DATE_SUB(NOW(), INTERVAL 3 DAY)

Might not be correct, it's been a while since I actually wrote a query, but you get the idea.

[Alechko]

Okey thanks,

they qurey dosen't working but It seems I get the idea...

 

thanks for the help!

[Barand]

Fixed Dan's WHERE clause

 

...WHERE CONCAT_WS('-', DATE_FORMAT(NOW(), '%Y'), DATE_FORMAT(birthdate, '%m-%d'))
BETWEEN CURDATE() + INTERVAL 1 DAY  AND CURDATE() + INTERVAL 3 DAY

Edited September 28, 2012 by Barand