MySQL Forums
Forum List  »  Quality Assurance

Problem with comparing date of birth with birthday
Posted by: Ritchie Tax
Date: May 10, 2011 05:31AM

Oke i have a problem with the queries one of my java applications is using.

Im programming a java application that takes the birthdays of today, yesterday, tomorrow, last week and rest of the year.

Now ive tried alot and got the queries for today, yesterday and tomorrow working but when im trying to make a query for last week i have a problem.

My database holds a collumn for the birthday of each employee listed in it.
Now im trying to get the birthdays (of the current year) of the employee's of last week.

The query's of yesterday and today are:

ResultSet yesterday = st.executeQuery(
SELECT medewerkers.*, afdelingen.naam AS afdelingnaam
FROM medewerkers, afdelingen
WHERE afdelingen.afdelingid = medewerkers.afdeling
AND DAY(geboortedatum) = DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
AND MONTH(geboortedatum) = MONTH(CURDATE())
AND YEAR(geboortedatum) < YEAR(CURDATE())

ResultSet vandaag = st.executeQuery(
SELECT medewerkers.*, afdelingen.naam AS afdelingnaam
FROM medewerkers, afdelingen
WHERE afdelingen.afdelingid = medewerkers.afdeling
AND DAY(geboortedatum) = DAY(CURDATE())
AND MONTH(geboortedatum) = MONTH(CURDATE())
AND YEAR(geboortedatum) < YEAR(CURDATE())

Now these 2 are working properly, but when i try to get the birthdays of last week i come as far as:

ResultSet lastweek = st.executeQuery(
SELECT medewerkers.*, afdelingen.naam AS afdelingnaam
FROM medewerkers, afdelingen
WHERE afdelingen.afdelingid = medewerkers.afdeling
AND DAY(geboortedatum) BETWEEN DAY(DATE_ADD(CURDATE(), INTERVAL -7 DAY)) AND DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
AND MONTH(geboortedatum) = MONTH(CURDATE())
AND YEAR(geboortedatum) < YEAR(CURDATE())

This will not work when its below the 7th of the month since it will then go back to the previous month and actualy return nothing at all.
The problem is that the birthdays are stored by there date-of-birth and not the celebration type of birthday. (the years are diffrent).

I tried removing the MONTH(geboortedatum) and YEAR(geboortedatum) but then ill still have the same annoying problem when the day is below the 7th of the month.
Also directly comparing geboortedatum with CURDATE() will not work either since the years are diffrent. I also then tried to get the diffrence in days %365 but not every year has 365 days 0.0

Can anyone help me out on how to make a propper query for this?

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with comparing date of birth with birthday
2914
May 10, 2011 05:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.