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.
syntax of date_format:
SELECT date_format(date_born, '%m/%d/%Y' ) as my_date FROM date_tbl
'%W %D %M %Y %T' -> Wednesday 5th May 2004 23:56:25
'%a %b %e %Y %H:%i' -> Wed May 5 2004 23:56
'%m/%d/%Y %T' -> 05/05/2004 23:56:25
'%d/%m/%Y' -> 05/05/2004
'%m-%d-%y' -> 04-08-13
Use the DATE function:
SELECT DATE(orders.date_purchased) AS date
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
(Select Date_Format(orders.date_purchased,'%m/%d/%Y')) As Date
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.
Source: Stackoverflow.com