MySQL 如何在mysql中将varchar转换为日期时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15396058/
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
how to convert varchar to datetime format in mysql
提问by Jaylen
I am trying to convert varchar to date time This is what I have now, and it is not working for me. I always get the have value
我正在尝试将 varchar 转换为日期时间这就是我现在所拥有的，它对我不起作用。我总是得到有价值的
STR_TO_DATE(REPLACE(LEFT('5/16/2011 20:14 PM', LOCATE('M' , '5/16/2011 20:14 PM')-3), '/',','),'%m-%d-%Y %T')
This following code returns 5,16,2011 20:14
以下代码返回 5,16,2011 20:14
select REPLACE(LEFT('5/16/2011 20:14 PM', LOCATE('M' , '5/16/2011 20:14 PM')-3), '/',',')
my current output is emply string now. it should be 2011-05-16 20:14:00
我现在的输出是 emply 字符串。应该是 2011-05-16 20:14:00
How can i get this to work?
我怎样才能让它工作？
thanks
谢谢
回答by fthiella
If your varchar is like this:
如果你的 varchar 是这样的：
5/16/2011 20:14 PM
you can convert it to datetime using this:
您可以使用以下方法将其转换为日期时间：
SELECT STR_TO_DATE('5/16/2011 20:14 PM', '%c/%e/%Y %H:%i')
or this to format it like you want:
或按照您的意愿对其进行格式化：
SELECT DATE_FORMAT(STR_TO_DATE('5/16/2011 20:14 PM', '%c/%e/%Y %H:%i'), '%Y-%m-%d %H:%m:%s')
