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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gamebitsCommented:
SELECT column1, date, column3, column4 from projects WHERE date LIKE '2010-04-04%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.