STR_TO_DATE incorrect datetime mysql

Question

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!

replace `/` by hyphen. `select STR_TO_DATE('17-08-2016 11:30:51 AM','%d-%m-%Y %h:%i:%s %p');` — 1000111, Aug 17 '16 at 06:43
the date format, I m getting is through service. So can't change it — Vikrant, Aug 17 '16 at 06:44

Jens · Accepted Answer · 2017-09-27T05:15:11.313

2

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');

edited Sep 27 '17 at 05:15

answered Aug 17 '16 at 06:43

Jens

67,715
15
98
113

score 0 · Answer 2 · answered Aug 17 '16 at 07:00

As Date from Service itself was coming wrong. So, service needs to be changed:

Previously was passing billObj.BillingDate of type string.

It changed to:

billObj.BillingDate = (DateTime.ParseExact(billObj.BillingDate, HotelApplication.AppCode.BusinessLayer.util.StaticDefinitions.appDateFormat, CultureInfo.InvariantCulture)).ToString();

And it worked out :) Thanks for all helps!

STR_TO_DATE incorrect datetime mysql

2 Answers2