MySQL str_to_date conversion for selected format

Question

Date conversion issue via STR_TO_DATE function in MySQL

SELECT STR_TO_DATE('17-JUL-14','%Y-%m-%d %h:%i:%s')

It shows as

 NULL

How can I show it as:

 2014-07-17 00:00:00

Thanks

Answer 1

You're trying to use STR_TO_DATE. You're feeding it 3 inputs, and saying insert into these 6 fields. First insert into the 3 appropriate inputs, then use DATE_FORMAT to get 0 for the additional fields.

SELECT DATE_FORMAT(STR_TO_DATE('17-JUL-14','%d-%b-%Y'), '%Y-%m-%d %H:%i:%s') from dual;

Also you're going to want to use %H, as the lower case will auto value with 12 instead of 0.

Answer 2

Here is output:

 2014-07-17 00:00:00

Using:

 SELECT STR_TO_DATE('17-JUL-14','%d-%M-%Y %h:%i:%s')

Hope it helps !

Answer 3

Already Answered above, but since I saw this,

what you are doing is

SELECT STR_TO_DATE('17-JUL-14','%Y-%m-%d %h:%i:%s')

Which means you are putting 17 into Year (map 1st to 1st value), accidentally month still gets the correct value, but the day gets 14 again. well that is wrong.

Why you are not getting the output at all, %Y, %M and %D uses capital letters.