I have birthdays stored in my db like 1988-10-22 format.
How can I pull out all the ones that are 14 day less than the upcoming date? so I can send out an email...
I just love dates in php and sql.
Thanks,
Scott
sql date subtraction
Moderator: General Moderators
You can use the MySQL Date and Time Functions http://www.mysql.com/doc/en/Date_and_ti ... tions.html. First use DATE_ADD function, add the fourteen day to the actual date, so you will have the "upcoming date" automaticaly. Then use the WEEKOFYEAR function and substract the WEEKOFYEAR("upcoming date") and the WEEKOFYEAR("birthday date"). So if the absolute result will be 2 or 1 it's means that the date is correct and should be pulled out from the database. Sorry for no examples, but I've just figured it out, and think that will work. 
...might be worth looking into. Combining that within the where clause of the query might help. (Found on the same page as delorian posted)DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
WEEKOFYEAR() is only available in Mysql 4.1.1+, and while not being set as stable, it's common that isp/hosts still are using the older version.
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
Select field1
,field2
from myDB
where someDate - 14 DAYS = Current Date
I <*think*> this is standard SQL - works in DB2 - think it works in MySQL as well. You should also be able to do the same with MONTHS
and YEARS. You could also change CURRENT DATE to some other date value you want to compare with.
Let the DB do the work it is optimized to do.
,field2
from myDB
where someDate - 14 DAYS = Current Date
I <*think*> this is standard SQL - works in DB2 - think it works in MySQL as well. You should also be able to do the same with MONTHS
and YEARS. You could also change CURRENT DATE to some other date value you want to compare with.
Let the DB do the work it is optimized to do.
If you're going to use PHP, most likely to get the dates and send the mails and all then you might want to check out mktime. Generate the date 14 days from the current date then match it with the date in the table. If it matches, then send the mail.
That should work nicely. Let me know,
-Nay
That should work nicely. Let me know,
-Nay