Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-13 : 09:46:02
|
| Robert writes "I need to search a database of names and dates of birth to get a list of people with birthdays in say the next 7 days no matter what year they were born. I have tried the following with some luck but not the total list I need."SELECT * FROM Clients WHERE DOB >= #'" & Format(Now, "mm/dd") & "# and <= #" & DateAdd(y, 7 , Format(Now, "mm/dd") & "#"These dates are not what I use since I use VB and choose today's date as well as today's date plus 7.Thanks for your help!!!" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2001-12-14 : 09:14:35
|
| not sure what the code would look like in access.....but step back a bit and re-think the problem.....One way to look at it is to say that everybody has a birthday on the same day every year....they were all born in 1 particular year, but each year they remember that 1st auspicous start...so...take their DOB, adjust it to get this years equivalent date and then do the maths on that adjusted date and your target date. On issue to look out for is where the target or adjusted date moves into a different year....like when somebody is born on Jan 2 and the target date is Dec 28....example....DOB = 17/05/1976....TODAY = 14/12/2001....Adjusted DOB = 17/05/2001.....do the comparison between the latter 2 values....Some form of dateadd + substring (or access equivalent) on the DOB should get you close to a solution. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-14 : 09:33:01
|
| Have to handle 29th Feb too. |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2001-12-14 : 18:06:27
|
| Suppose we have a table of integers in unbroken sequence from 0 up to 6 at least:numbers (anint integer unique not null)------0123456...A table of integers like this is a useful thing to kepp around.Now join the Clients on date and month of DOB to the the date and month of the next 7 days. select Clients.*from Clients INNER JOIN (select month(dateadd(day, anint + 1, startDate)) as MonthOfDayInNextN, day(dateadd(day, anint + 1, startDate)) AS DayInNextN from numbers where anint < 7) AS NextSevenDays ON (DAY(dob) = NextSevenDays.DayInNextN AND month(DOB) = NextSevenDays.MonthOfDayInNextN) OR 'the unlucky ones born the 29th Feb celebrate on the day after the 28th Feb: ((DAY(DOB) = 29 AND MONTH(DOB) = 2 AND DayInNextN = 1 AND MonthOfDayInNextN = 3))The Access date functions may work a little differenly. |
|
|
|
|
|
|
SQL search upcoming birthdays