Incorrect date time format in update statement
I am using MySQL database. I have an update statement and I am trying to update a data table from an ASP.NET SQL query.
UPDATE customer_request
SET Issue = 'Broken_Wire',
FixedDate = '2015-05-17 14:05:46'
WHERE CustReqID = 102
AND ErrorReportedDate = STR_TO_DATE('5/17/2015 11:42:26 AM','%Y-%m-%d %H:%i:%s')
I am getting this error:
Error code: 1411. Invalid date / time value: '5/17/2015 11:42:26 AM' for str_to_date function
What is wrong with my request?
+3
source to share
1 answer
This part of your where clause is setting your date format incorrectly
STR_TO_DATE('5/17/2015 11:42:26 AM','%Y-%m-%d %H:%i:%s')
instead you probably want
STR_TO_DATE('5/17/2015 11:42:26 AM','%c/%e/%Y %H:%i:%s')
Where
%e = Day of the month without leading zero e.g., 1,2,…31
%c = Month in numeric e.g., 1, 2, 3…12
%Y = Four digits year e.g., 2000, 2001
%H = Hour with 24-hour format with leading zero e.g., 00..23
%i = Minutes with leading zero e.g., 00, 01,…59
%S or %s = Seconds with leading zero 00,01,…59
You can read the DATE_FORMAT specifiers here
+1
source to share