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
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
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
How can i get this to work?
thanks
解决方案
If your varchar is like this:
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')