MySQL数据库的正确DateTime格式是什么? [英] What is the correct DateTime format for MySQL Database?

问题描述

我正在使用以下SQL字符串将此DateTime数据'12/21/2012 1:13:58 PM'插入我的MySQL数据库:

I am Inserting this DateTime data '12/21/2012 1:13:58 PM' into my MySQL Database using this SQL string:

String Query = "INSERT INTO `restaurantdb`.`stocksdb` 
                  (`stock_ID`,`stock_dateUpdated`) 
                  VALUES ('@stockID' '@dateUpdated');

，我收到此错误消息:

Incorrect datetime value: '12/21/2012 1:13:58 PM' for column 'stock_dateUpdated' at row 1

那么dateTime输入到MySQL数据库的正确格式/值是什么?

So what is the right format/value for dateTime to input into a MySQL database?

推荐答案

问:什么是MySQL语句中DATETIME文字的正确格式/值?

Q: What is the right format/value for DATETIME literal within a MySQL statement?

A:在MySQL中，DATETIME文字的标准格式为:

A: In MySQL, the standard format for a DATETIME literal is:

 'YYYY-MM-DD HH:MI:SS'

，其中时间部分为24小时制(即以00到23之间的值表示的小时数字).

with the time component as a 24 hour clock (i.e., the hours digits provided as a value between 00 and 23).

MySQL提供了内置函数STR_TO_DATE，该函数可以将各种格式的字符串转换为DATE或DATETIME数据类型.

MySQL provides a builtin function STR_TO_DATE which can convert strings in various formats to DATE or DATETIME datatypes.

因此，作为替代方案，您也可以通过调用该函数来指定DATETIME的值，如下所示:

So, as an alternative, you can also specify the value of a DATETIME with a call to that function, like this:

STR_TO_DATE('12/21/2012 1:13:58 PM','%m/%d/%Y %h:%i:%s %p')

因此，如果您的VALUES列表如下所示，则可以让MySQL在INSERT语句中为您进行转换:

So, you could have MySQL do the conversion for you in the INSERT statement, if your VALUES list looked like this:

... VALUES ('@stockID', STR_TO_DATE('@dateUpdated','%m/%d/%Y %h:%i:%s %p');

(我注意到您在VALUES列表中的两个文字之间缺少必需的逗号.)

(I notice you have a required comma missing between the two literals in your VALUES list.)

MySQL确实允许DATETIME文字部分之间的分隔符有一定的自由度，因此不是严格要求的.

MySQL does allow some latitude in the delimiters between the parts of the DATETIME literal, so they are not strictly required.

MySQL 5.5参考手册.

这篇关于MySQL数据库的正确DateTime格式是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！