asked on

URGENT: need help formatting 'date' for sql query

I want to pull rows from my sql database, based on a specific date (stored in the column "date"), but the date is stored in the database in format "YYYY-MM-DD HH-MM-SS" and I need to convert it during the query so that it only pulls based on the date.

Here is what I have so far, which isn't working:

SELECT column1, date, column3, column4 from projects WHERE DATE_FORMAT(date, 'Y-m-d"')='2010-04-04'

What am I doing wrong?

ASKER CERTIFIED SOLUTION

gamebits

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anotherdotcommer

ASKER

Awesome! Now one more question: what would the php code be that would generate yesterdays date in YYYY-MM-DD format? The purpose of this code is to pull projects that were started the previous day, so the code would read:

SELECT column1, date, column3, column4 from projects WHERE date LIKE 'YESTERDAYS DATE IN YYYY-MM-DD FORMAT%'

Nem Schlecht

No need for PHP, you can do it in MySQL:

This part:
DATE(DATE_SUB(date, INTERVAL 1 DAY))

Gives you midnight of the previous day.

SELECT column1, date, column3, column4 from projects
WHERE date > DATE(DATE_SUB(date, INTERVAL 1 DAY))
;

Open in new window

anotherdotcommer

ASKER

Hmm, that's just pulling up EVERYTHING prior to today... not just stuff from yesterday.

gamebits

Not sure if I understand but it should work if you use yesterday's date

SELECT column1, date, column3, column4 from projects WHERE date LIKE '2010-04-03%'

Nem Schlecht

Just stuff from yesterday:

SELECT column1, date, column3, column4 from projects
WHERE DATE(date) = DATE(DATE_SUB(date, INTERVAL 1 DAY))
;

Open in new window