FlexFiend FlexFiend - 1 year ago 72
MySQL Question

MySql String To Date Malfunction

I've got a MySQL db that of course, stores a DateTime in the form of a string. I'm trying to convert this to a DateTime so I can filter by year, Month and so on.

SELECT breadCrumbDateTime, STR_TO_DATE(breadCrumbDateTime, '%m/%d/%y %H:%i:%s')


And the value of 'breadCrumbDateTime' looks like this.

'07/15/2017 15:05:16'

However, the STR_TO_DATE function always returns null.

enter image description here

What is the proper format that I should be using to get the STR_TO_DATE function to behave correclty.

Any help is greatly appreciated.

Answer Source
SELECT STR_TO_DATE('07/15/2017 15:05:16','%m/%d/%Y %H:%i:%s')

You can try above code.

You are trying to put '%y' instead of it you have to put '%Y'

Note that you should probably not be storing your dates as text. Having to call STR_TO_DATE() each time you need to do a date manipulation will get very old, very fast.

Hope this will help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download