MySql String To Date Malfunction

Question

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.

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

Answer 1

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.