It's not trivial. You have to worry about wrapping around to the next year.

Easiest way to do this in MySQL is to use the DAYOFYEAR() function to transform the person's birthdate into a date in the current year:

Code:

WHERE DATEADD( DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY ), INTERVAL DAYOFYEAR(birthdate) DAY )
BETWEEN CURDATE() AND DATEADD( CURDATE(), INTERVAL 7 DAY )

You see it?

Start with CURDATE(). Subtract from that the number of days in the year it represents. That gives you December 31st of prior year. To that, you add the number of days the birthdate represents.

Then you check that to see if that result is between today and 7 days from now.

Example: Let's pretend today is Feb 4th, 2013. And person's birthday is Jan 28, 1970.

Okay, in parts:

Code:

DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY )
becomes
DATESUB( '2013-2-4', INTERVAL DAYOFYEAR('2013-2-4' DAY )
which is
DATESUB( '2013-2-4', INTERVAL 35 DAY )
which is
'2012-12-31'
So then
DATEADD( DATESUB( CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY ), INTERVAL DAYOFYEAR(birthdate) DAY )
becomes
DATEADD( '2012-12-31', INTERVAL DAYOFYEAR(birthdate) DAY )
which becomes
DATEADD( '2012-12-31', INTERVAL DAYOFYEAR('2011-1-27') DAY )
which becomes
DATEADD( '2012-12-31', INTERVAL 27 DAY )
which becomes
'2013-1-27'
And we finally do
WHERE '2013-1-27' BETWEEN CURDATE() AND DATEADD( CURDATE(), INTERVAL 7 DAY )
which becomes
WHERE '2013-1-27' BETWEEN '2013-2-4' AND DATEADD( '2013-2-4', INTERVAL 7 DAY )
which is finally
WHERE '2013-1-27' BETWEEN '2013-2-4' AND '2013-2-11'

Which means it will be false.

But if the birth date had been '1970-2-8' (for example) you can surely see how it WOULD be between those two dates and so the WHERE would be true.

** UNTESTED ** but I think it should work universally, even in leap years and around Feb 29th.