mysql更改日期格式 [英] mysql change date format
本文介绍了mysql更改日期格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个日期字段(tinytext),其中保存着日期信息,格式为"dd-mm-yy",例如07-01-90.使用mysql查询,我想将其更改为yyyy-mm-dd日期格式.我尝试了下面的代码,但没有任何反应.
I have a date field (tinytext) holding date information in format of "dd-mm-yy" e.g 07-01-90. Using a mysql query I want to change it to yyyy-mm-dd date format. I tried the code below but nothing happens.
mysql_query("UPDATE Table SET date=STR_TO_DATE('date','%Y,%m,%d')");
推荐答案
您正在使用正确的函数STR_TO_DATE(str,format)
来实现目标,但您犯了两个错误:
You're using the correct function STR_TO_DATE(str,format)
to achieve the goal, but you're making two mistakes:
- 查询中的
format
参数与字符串表达式格式不匹配.当您将%Y,%m,%d
(逗号分隔)传递给format
参数时,您说它是dd-mm-yy
格式.该查询将返回日期时间值错误"错误.您应该使用%d-%m-%Y
. - 您不能通过设置要传递的值的其他类型来即时更改列的数据类型.您必须先更新值,然后更改列的数据类型.
- In your query the
format
argument does not match the string expression format. You said it's indd-mm-yy
format while you passed%Y,%m,%d
(comma separated) to theformat
argument. The query will return a "incorrect datetime value" error. You should use%d-%m-%Y
. - You can't change data type of a column on the fly, by setting different type of the value being passed. You have to first update the values and then change data type for column.
所以,总结一下:
mysql_query("UPDATE `Table` SET `date` = STR_TO_DATE(`date`, '%d-%m-%Y')");
mysql_query("ALTER TABLE `Table` CHANGE COLUMN `date` `date` DATE");
另外,请考虑切换到建议的 PDO 扩展名,代替原来的已弃用 mysql
扩展名.
Additionally, consider switching to the recommended PDO extension in place of old and slowly deprecated mysql
extension.
这篇关于mysql更改日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文