Incorrect date value: for column 'Date' at row 1?

Question

I have created a rest web api using Asp.net. When I tried to insert data to my table using post, I'm getting this exception: Incorrect date value: '07/11/2017 12:00:00 AM' for column 'Date' at row 1. What is the correct date value to insert to Mysql database?

public long addmeterreadingrecord(meterreadingrecords meterreadingrecord )
{
  MySqlConnection myconn = getDBConn();
  String sqlstr = 
  "INSERT INTO meterreadingrecords (ID,Date,User,ImageLink,ActualMeterReadingID) VALUES ('" 
          + meterreadingrecord.Id + "','" + meterreadingrecord.Date + "','" 
          + meterreadingrecord.User + "','" + meterreadingrecord.Imagelink+"','"
          + meterreadingrecord.ActualmeterreadingId+"')";
        MySqlCommand cmd = new MySqlCommand(sqlstr, myconn);
        myconn.Open();
        cmd.ExecuteNonQuery();
        myconn.Close();
        myconn.Dispose();
        long id = cmd.LastInsertedId;
        return id;
    }

How I inserted data using a rest client.

{
"ID" : "zzz",
"Date": "11/07/2017",
"User" : "Me",
"ImageLink" : "testimg",
"ActualMeterReadingID" : "ccc"
}

Data type for date in mysql db is Date. Not DateTime.

Please avoid samples demonstrating SQL injection - as it may cause negative reaction to post neglecting good practices. [Edit] post to use parametrized queries as shown in https://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp. — Alexei Levenkov, Jul 11 '17 at 03:16

cwallenpoole · Accepted Answer · 2017-07-11T03:23:16.933

0

MySQL expects a four digit year followed by a two-digit month and a two-digit day. Yesterday's date (Jul. 9) is:

'2017-07-09'

MySQL does make some allowances for alternate format (including accepting a : as delimiter), but it still expects the format to be year-month-date. The value 11/07/2017 cannot be a correct date by this standard. In order to account for this, you'll want to use STR_TO_DATE.

STR_TO_DATE('11/07/2017', '%d/%m/%Y')

Also, for reference, this is a list of valid date string % variables.

edited Jul 11 '17 at 03:23

answered Jul 11 '17 at 02:57

cwallenpoole

79,954
26
128
166

I tried as you said. But getting this exception now Truncated incorrect date value: '11/07/2017 12:00:00 AM' String sqlstr = "INSERT INTO meterreadingrecords (ID,Date,User,ImageLink,ActualMeterReadingID) VALUES ('" + meterreadingrecord.Id + "',STR_TO_DATE('" + meterreadingrecord.Date + "', '%d/%m/%Y'),'" + meterreadingrecord.User + "','"+meterreadingrecord.Imagelink+"','"+meterreadingrecord.ActualmeterreadingId+"')"; – batwing Jul 11 '17 at 03:05
Have you tried `%d/%m/%Y %H:%i:%s`? You might need to play with the formatting a little to get it working. – cwallenpoole Jul 11 '17 at 03:22

score 0 · Answer 2 · answered Jul 11 '17 at 03:01

0

MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format

The DATE, DATETIME, and TIMESTAMP Types

answered Jul 11 '17 at 03:01

Ekaba Bisong

2,918
2
23
38

Incorrect date value: for column 'Date' at row 1?

2 Answers2