Query to convert from datetime to date mysql

SqlMysqlDatetimeDate

Sql Problem Overview

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:

https://stackoverflow.com/questions/4596457/mysql-query-format-date-on-output

Any help is appreciated.

Sql Solutions

Solution 1 - Sql

Try to cast it as a DATE

SELECT CAST(orders.date_purchased AS DATE) AS DATE_PURCHASED

Solution 2 - Sql

Use the DATE function:

SELECT DATE(orders.date_purchased) AS date

Solution 3 - Sql

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.

Solution 4 - Sql

I see the many types of uses, but I find this layout more useful as a reference tool:

SELECT DATE_FORMAT('2004-01-20' ,'%Y-%m-01');

enter image description here

Solution 5 - Sql

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
Categories
Recommended Sql Solutions
Recommended Mysql Solutions
Recommended Datetime Solutions
Recommended Date Solutions

Previous Solution:

Format Date output in JSF

JsfElDate Formatting

Next Solution:

How can I move all the files from one folder to another using the command line?

Batch FileCmdCommand Prompt

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionchupemanView Question on Stackoverflow
Solution 1 - SqlChanduView Answer on Stackoverflow
Solution 2 - SqlOMG PoniesView Answer on Stackoverflow
Solution 3 - SqlMike Sherrill 'Cat Recall'View Answer on Stackoverflow
Solution 4 - Sqlmanish1706View Answer on Stackoverflow
Solution 5 - SqlDedi KoswaraView Answer on Stackoverflow