sql date subtraction

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
clownbarf
Forum Newbie
Posts: 6
Joined: Fri Sep 19, 2003 10:14 am

sql date subtraction

Post 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
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post 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
clownbarf
Forum Newbie
Posts: 6
Joined: Fri Sep 19, 2003 10:14 am

Post 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 :(
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
Post Reply