Take the 2-minute tour ×

I am trying to get the date portion of a datetime field. I know I can get it with date_format, but that returns a string or "varchar" field. How can I convert the result to date and not as varchar?

This is my query returning the varchar:

(Select Date_Format(orders.date_purchased,'%m/%d/%Y')) As Date  

I tried several combinations from this question, but could not make it to work:

mysql query - format date on output?

Any help is appreciated.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

Try to cast it as a DATE

share|improve this answer
Thanks! This is what I needed. =o) –  chupeman Jan 19 '11 at 22:24
add comment

Use the DATE function:

SELECT DATE(orders.date_purchased) AS date
share|improve this answer
This is the best answer. –  Nathanael Shermett May 17 at 19:02
add comment

Either Cybernate or OMG Ponies solution will work. The fundamental problem is that the DATE_FORMAT() function returns a string, not a date. When you wrote


I think you were essentially asking MySQL to try to format the values in "date_purchased" according to that format string, and instead of calling that column "date_purchased", call it "Date". But that column would no longer contain a date, it would contain a string. (Because Date_Format() returns a string, not a date.)

I don't think that's what you wanted to do, but that's what you were doing.

Don't confuse how a value looks with what the value is.

share|improve this answer
add comment

Your Answer


