Search code examples
mysqldatetimestr-to-date

STR_TO_DATE incorrect datetime mysql

I have tried to search many questions about this.. But it seems telling solutions about Quotes use.

references : 1, 2, 3, 4

In my case,

select STR_TO_DATE('17-08-2016 11:30:51 AM','%d/%m/%Y %h:%i:%s %p');

gives output NULL.
And In INSERT statement:

INSERT INTO BILLING(BILL_NUMBER,BILLING_DATE)
VALUES ('1',STR_TO_DATE('17-08-2016 11:30:51 AM','%d/%m/%Y %h:%i:%s %p'));

It throws Error :

Error Code: 1411. Incorrect datetime value: '17-08-2016 11:30:51 AM' for function str_to_date

NOTE: 1. Date format is fixed > '%d/%m/%Y %h:%i:%s %p'
2. And String format is also fixed > '17-08-2016 11:30:51 AM' which I am capturing from C# Service.

Thanks In Advance!

Solution

  • Your Format does not match the string. Change the slashes to -

    select STR_TO_DATE('17-08-2016 11:30:51 AM','%d-%m-%Y %h:%i:%s %p');