MySQL 日期格式 - 插入日期的困难
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9907210/
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 formats - difficulty Inserting a date
提问by Phil
I am trying to further a question I asked yesterday where I wanted to know how to query a date in a different format. But now I am trying to do an insert using this method (see below) however I can't get it to work. I have checked the manual but it is not beginner friendly!
我试图进一步解决我昨天问的一个问题，我想知道如何以不同的格式查询日期。但是现在我正在尝试使用这种方法（见下文）进行插入，但是我无法让它工作。我已经检查了手册，但它对初学者不友好！
INSERT INTO custorder VALUES ('Kevin','yes'), STR_TO_DATE('1-01-2012', '%d-%m-%Y');
回答by ypercube??
Put the date in single quotes and move the parenthesis (after the 'yes') to the end:
将日期放在单引号中并将括号（在 之后'yes'）移到末尾：
INSERT INTO custorder
VALUES ('Kevin', 'yes' , STR_TO_DATE('1-01-2012', '%d-%m-%Y') ) ;
^ ^
---parenthesis removed--| and added here ------|
But you can always use dates without STR_TO_DATE()function, just use the (Y-m-d) '20120101'or '2012-01-01'format. Check the MySQL docs: Date and Time Literals
但是你总是可以使用没有STR_TO_DATE()函数的日期，只需使用 (Ymd)'20120101'或'2012-01-01'格式。检查 MySQL 文档：日期和时间文字
INSERT INTO custorder
VALUES ('Kevin', 'yes', '2012-01-01') ;
回答by Gleeb
Looks like you've not encapsulated your string properly. Try this:
看起来你没有正确封装你的字符串。尝试这个：
INSERT INTO custorder VALUES ('Kevin','yes'), STR_TO_DATE('1-01-2012', '%d-%m-%Y');
Alternatively, you can do the following but it is not recommended. Make sure that you use STR_TO-DATE it is because when you are developing web applications you have to explicitly convert String to Date which is annoying. Use first One.
或者，您可以执行以下操作，但不建议这样做。确保您使用 STR_TO-DATE，因为在开发 Web 应用程序时，您必须将字符串显式转换为日期，这很烦人。使用第一个。
INSERT INTO custorder VALUES ('Kevin','yes'), '2012-01-01';
INSERT INTO custorder VALUES ('Kevin','yes'), '2012-01-01';
I'm not confident that the above SQL is valid, however, and you may want to move the date part into the brackets. If you can provide the exact error you're getting, I might be able to more directly help with the issue.
但是，我不确定上述 SQL 是否有效，您可能希望将日期部分移到括号中。如果你能提供你得到的确切错误，我可能能够更直接地帮助解决这个问题。
回答by Sachin Mallade
The date format for mysql insert query is YYYY-MM-DD
mysql 插入查询的日期格式是 YYYY-MM-DD
example:
例子：
INSERT INTO table_name (date_column) VALUE ('YYYY-MM-DD');
回答by Kulasangar
An add-on to the previous answers since I came across this concern:
自从我遇到这个问题以来，对先前答案的补充：
If you really want to insert something like 24-May-2005to your DATEcolumn, you could do something like this:
如果你真的想在24-May-2005你的DATE列中插入类似的东西，你可以这样做：
INSERT INTO someTable(Empid,Date_Joined)
VALUES
('S710',STR_TO_DATE('24-May-2005', '%d-%M-%Y'));
In the above query please note that if it's May(ie: the month in letters) the format should be %M.
在上面的查询中，请注意，如果是May（即：字母中的月份），格式应该是%M.
NOTE: I tried this with the latest MySQL version 8.0 and it works!
注意：我用最新的 MySQL 8.0 版尝试了这个，它工作正常！
回答by George Fisher
When using a string-typed variable in PHP containing a date, the variable must be enclosed in single quotes:
在 PHP 中使用包含日期的字符串类型变量时，该变量必须用单引号括起来：
$NEW_DATE = '1997-07-15';
$sql = "INSERT INTO tbl (NEW_DATE, ...) VALUES ('$NEW_DATE', ...)";
