查询从日期时间转换为日期 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4740612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to convert from datetime to date mysql
提问by chupeman
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?
我正在尝试获取日期时间字段的日期部分。我知道我可以使用 date_format 获取它，但这会返回一个字符串或“varchar”字段。如何将结果转换为日期而不是 varchar？
This is my query returning the varchar:
这是我返回 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.
任何帮助表示赞赏。
回答by Chandu
Try to cast it as a DATE
尝试将其转换为 DATE
SELECT CAST(orders.date_purchased AS DATE) AS DATE_PURCHASED
回答by OMG Ponies
回答by Mike Sherrill 'Cat Recall'
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
Cybernate 或 OMG Ponies 解决方案都可以使用。根本问题是该DATE_FORMAT()函数返回一个字符串，而不是一个日期。当你写
(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_purchasedaccording 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.)
我认为您实际上是在要求 MySQL 尝试date_purchased根据该格式字符串格式化值，而不是调用该列date_purchased，而是将其称为“日期”。但该列将不再包含日期，而是包含一个字符串。（因为Date_Format()返回的是字符串，而不是日期。）
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.
不要混淆值的外观和值是什么。
回答by Dedi Koswara
syntax of date_format:
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
回答by manish1706
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');
