PHP SQL - MySQL Week function

Author

MySQL Week function

Bas Varkevisser

2005-01-24, 3:59 pm

Hi,

I have a table with birthdates and only want the persons that have their
birthday in this w

.

I use SELECT * FROM persons WHERE w

(birthdate) = w

(NOW());

That returns birthdays from last sunday until next tuesday(!)

Does anyone know why it returns too many days?

Thanks,
Bas.

Steve

2005-01-25, 3:57 pm

> I have a table with birthdates and only want the persons that have
their
> birthday in this w

.

You need to ask if the person's birthday THIS YEAR falls in this w

.

You are actually asking if the person's birthday in the year of their
birth fell the same number of w

s after the start of the year as
today's date does for this year. This is not a useful question for the
purpose...

Substitute the current year into the person's birthdate before asking
the question:

SELECT *
FROM persons
WHERE WEEK(
CONCAT( YEAR( NOW() ),
CONCAT( "-",
CONCAT( MONTH( Birthdate ),
CONCAT( "-", DAYOFMONTH( Birthdate )
)
)
)
)
) = WEEK( NOW() )
FROM mytable

---
Steve

Sponsored Links