Grab Last Three Weeks with STR_To_Date

Databases
1

I have a bunch of films in a table with release dates formatted like: November 12, 2011

I want to grab all the films that have been released to theaters in the past three weeks. The release date column is ‘release’.

I tried this


"SELECT title, keyw, views, release 
FROM films 
WHERE STR_TO_DATE(release, '%M %e, %Y') <= DATE_SUB(CURDATE(),INTERVAL 21 DAY) 
ORDER BY views DESC 
LIMIT 8

I’m getting a mysql error claiming it’s not geting this. Something I’m overlooking?

Thanks
Ryan

2

My mysql server version is 5.1.56
client version: 4.1.22

3

you’ve got “where release date is earlier than 3 weeks ago”

i believe you want “where release date is later than 3 weeks ago”

4

I changed to:


SELECT title, fkeyw, views, release 
FROM films 
WHERE STR_TO_DATE(release, '%M %e, %Y') >= DATE_SUB(CURDATE(),INTERVAL 21 DAY) 
ORDER BY views DESC 
LIMIT 8

And still getting error: “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 ‘release FROM films WHERE STR_TO_DATE(release, ‘%M %e, %Y’) >= DATE_SUB(CURDATE()’ at line 1”

Thanks
Ryan

5

oh, that’s different – an error!!

it turns out that RELEASE is a reserved word – wrap it in backticks

:slight_smile: