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?
anotherdotcommerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gamebitsConnect With a Mentor Commented:
SELECT column1, date, column3, column4 from projects WHERE date LIKE '2010-04-04%'
0
 
anotherdotcommerAuthor Commented:
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%'
0
 
nemws1Database AdministratorCommented:
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

0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
anotherdotcommerAuthor Commented:
Hmm, that's just pulling up EVERYTHING prior to today... not just stuff from yesterday.
0
 
gamebitsCommented:
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%'
0
 
nemws1Database AdministratorCommented:
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

0
All Courses

From novice to tech pro — start learning today.