Page 1 of 1

sql date subtraction

Posted: Mon Sep 29, 2003 9:54 am
by clownbarf
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

Posted: Mon Sep 29, 2003 10:28 am
by delorian
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. :D

Posted: Mon Sep 29, 2003 12:23 pm
by clownbarf
is this all in one query?
thanks,
I'm not sure about the second part. I want every one in the database from todays date + 14 reguardless of the year..
+ WEEKOFYEAR is not in my hosts version of sql :(

Posted: Mon Sep 29, 2003 3:51 pm
by JAM
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
...might be worth looking into. Combining that within the where clause of the query might help. (Found on the same page as delorian posted)

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.

Posted: Sat Oct 04, 2003 11:58 pm
by fractalvibes
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.

Posted: Sun Oct 05, 2003 1:36 am
by Nay
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