0

I'm building a parameterized MySql query in C#. I'm having a problem with the endDate parameter.

cmd.Parameters.Add("@enddate", MySqlDbType.Date).Value = endDate.Date;

endDate is DateTime variable. The date column in the table is Date. When the code is running against MySql 5.7 all is well. Running against mySql 8 I get an "Incorrect DATE value: ''" error. I have tried many variants of handling the date. My last effort was this

string strEndDate = endDate.ToString("yyyy-MM-dd");
cmd.Parameters.Add("@enddate", MySqlDbType.Date).Value = DateTime.ParseExact(strEndDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);

Every effort ends in the "Incorrect DATE value: ''" error. From this message it seems the parameter is seen as ''.

How do I get this thing to work with MySql v8?

Mark Clark
  • 157
  • 2
  • 14
  • 1
    That's an empty string, so somewhere you're either formatting it incorrectly, or providing the binding incorrectly. – tadman Feb 06 '23 at 18:41
  • 2
    Why are you formatting to a string, then immediately parsing? – tadman Feb 06 '23 at 18:42
  • use the string directly and don't try to convert it – nbk Feb 06 '23 at 18:42
  • https://stackoverflow.com/questions/68544755/mysql-1525-incorrect-date-value-error-when-selecting-a-blank-date#comment121138680_68544755? – GSerg Feb 06 '23 at 18:49
  • I've tried iusing the DateTime variable(which was the original code above) as-is. Worked in 5.7. Formatting to a string was a suggestion on this SO. I've also tried using the string as-is. Nothing so far has worked – Mark Clark Feb 06 '23 at 18:53
  • The minimum value acceptable for a date column is '1000-01-01'. https://dev.mysql.com/doc/refman/8.0/en/datetime.html This probably is the cause of the error if the DateTime variable has its default value of '0001-01-01'. – Steve Feb 06 '23 at 19:03

1 Answers1

0

It wasn't the enddate's formatting issue. A few lines above I was testing for the date field being empty. V8 doesn't like datefield = '' any longer so I'm just testing for IS NULL

Mark Clark
  • 157
  • 2
  • 14
  • 2
    Perhaps you should delete both the question and the answer. There is no great benefits for future readers in a very local situation like this one. – Steve Feb 06 '23 at 21:56