[RESOLVED] SQL question: today-30?

[NeAlvey06]

Hi all, I'm trying to do something quite simple but having a heck of a time. I'm sure it's been discussed before but I haven't been able to locate a thread on it yet. If you know of one and can direct me, that would be great.

I have a date field "DateAdded" in my MySQL database. I'm trying to create a query that lists all records where the DateAdded is within the past 30 days? Something like where DateAdded >= today -30.

Currently I'm using:

$sql="SELECT MOD.ID, MOD.Name, MOD.DateAdded FROM tblmodels as MOD WHERE MOD.DateAdded >= DateAdd(\"d\",-30,Date()) ORDER BY MOD.ID";

I get the following generic message:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '("d",-30,Date()) ORDER BY MOD.ID' at line 3

Any ideas or alternatives? Thanks in advance!!

[bpat1434]

Use SUBDATE(datefield, INTERVAL 30 days) instead of DATEADD().

mySQL Date and Time Functions
Read up on them, they'll help you a lot.

Now, assuming that DateAdded is of type DATETIME or DATE format. Timestamps can be used, but require 1 more mySQL function FROM_UNIXTIME to convert it to a date.

// SQL for DateTime or Date formatted fields
$sql = "SELECT ID, Name, DateAdded 
FROM tblmodels
WHERE DateAdded >= SUBDATE(DateAdded, INTERVAL 30 DAY) ORDER BY ID ASC";

// SQL for a TIMESTAMP formatted field
$sql = "SELECT ID, Name, DateAdded 
FROM tblmodels
WHERE DateAdded >= SUBDATE(FROM_UNIXTIME(DateAdded, '%m-%d-%Y'), INTERVAL 30 DAY) ORDER BY ID ASC";

Hope that helps.

~Brett

[NeAlvey06]

Awesome, thank you very much. I ended up using:

WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= MOD.DateAdded

I would've sworn I looked at the page you gave me, however I clearly didn't as the very first thing listed on there was the query I needed. Thanks again!

[bpat1434]

No problem. That's what we're here for.

~Brett