MySQL MySQL日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5367721/
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
MySQL date format
提问by SohailRajput
When I make a table and create a field of type date in mysql, it stores date like 0000-00-00. Is it possible to change the format to 'd-m-Y'?
当我创建一个表并在 mysql 中创建一个日期类型的字段时，它存储的日期类似于 0000-00-00。是否可以将格式更改为“dm-Y”？
回答by RichardTheKiwi
Go to MySQL Reference - 10.5. Data Type Storage Requirements
Search for: Storage Requirements for Date and Time Types
搜索：日期和时间类型的存储要求
Dates are internally stored as A three-byte integer packed as DD + MM×32 + YYYY×16×32
日期在内部存储为 A three-byte integer packed as DD + MM×32 + YYYY×16×32
But, if you select a date column for display, it has to be shown in someway, so it comes out as 0000-00-00. It is notstored as a char(10) with that specific format.
但是，如果您为 display选择一个日期列，它必须以某种方式显示，所以它显示为 0000-00-00。它不存储为具有该特定格式的 char(10)。
If, for display, you need to see a specific format, you can convert it to VARCHAR in a specific format using Date_Format(). However, bear in mind that if you are using the query in a programming tool, this is notwhat you want to do. You want the raw date value, for display purposes, there will be a similar formatting function from whatever programming environment you use.
如果为了显示，您需要查看特定格式，您可以使用Date_Format()将其转换为特定格式的 VARCHAR 。但是，请记住，如果您在编程工具中使用查询，这不是您想要做的。您需要原始日期值，出于显示目的，无论您使用何种编程环境，都会有类似的格式化功能。
As you can see from the reference in DATE_FORMAT, you will want to use '%d-%m-%Y', e.g.
正如您从 DATE_FORMAT 中的参考所见，您将需要使用'%d-%m-%Y'，例如
SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1...
FROM sometable
....
回答by Pentium10
No, it's not possible. But you can use DATE_FORMAT to select that way.
不，这不可能。但是您可以使用 DATE_FORMAT 来选择这种方式。
SELECT Date_format(mydatefield, '%d-%m-%Y')
FROM table
回答by Matteo Riva
No, it's not possible while keeping it as a date field. I suggest you keep it in that format - so you can use all mysql date functions - and change it only when you display it to the users.
不，将其保留为日期字段是不可能的。我建议你保持这种格式——这样你就可以使用所有的 mysql 日期函数——并且只有在你向用户显示它时才改变它。
You can either do it application side or directly with a query:
您可以在应用程序端或直接使用查询来完成：
SELECT DATE_FORMAT(date_field, "%d-%m-%Y") FROM ...
回答by Sandy
You can do this using SQL
您可以使用 SQL 执行此操作
SELECT DATE_FORMAT(date_field, "%d-%m-%Y") FROM ...
Or if you're using PHP you can achieve the same results:
或者，如果您使用 PHP，则可以获得相同的结果：
echo date('d-m-Y', $originalDate); //You will get something like 09-08-2013 (Aug 8th, 2013)
echo date('j-n-y', $originalDate); //You will get something like 9-8-13 (Aug 8th, 2013)
回答by manish1706
I see the use for both, but I find this layout more useful as a reference tool:
我看到了两者的用途，但我发现此布局作为参考工具更有用：
SELECT DATE_FORMAT('2004-01-20' ,'%Y-%m-01');
