I have an issue when i tried to put a date into a date row in MySQL resulting in this error:

But my code is this:

DateTime tomarIngreso = dateTimePicker_FechaDeIngreso_Agregar.Value;
DateTime tomarPlazo = dateTimePicker_FechaPlazo_Agregar.Value;

string plazoFormat = tomarPlazo.ToString("yyyy-MM-dd");
string ingresoFormat = tomarIngreso.ToString("yyyy-MM-dd");

DateTime plazo = DateTime.ParseExact(plazoFormat, "yyyy-MM-dd", CultureInfo.InvariantCulture);
DateTime ingreso = DateTime.ParseExact(ingresoFormat, "yyyy-MM-dd", CultureInfo.InvariantCulture);

The query apparently works but when i look in phpMyAdmin i get this:

When i launch the program again it says the same MessageBox error and the rows with 0000-00-00 in the date can't appear in the dataGridView.

How can I resolve this?

I think is the parsing method i use for dateTimePicker i have in this format:

So, when i trying to parse from dd-MM-yyyy to yyyy-MM-dd it's kinda failing or something like that.

The columns Ingreso and Plazo have Date in the type of data. I tried with Datetime in MySQL column but it doesn't works and causes glitches in the program. I don't need Datetime to be the data type, just Date.

CodePudding user response：

The issue is 0000-00-00 is not a valid Date, even though it looks like one. We know this, because how would that value answer questions like "What day of the week is this?" or "What month is this?".

Therefore, you will NEVER be able to parse this input as a C# DateOnly or DateTime value. Instead, you'll need to have code somewhere (maybe in the SQL, maybe in the reporting tool, maybe in the C#) to detect it and decide how you want it to display.

CodePudding user response：

If the NO_ZERO_DATE mode is not enabled on your MySQL Server, then MySQL will allow 0000-00-00 to be stored as a "dummy" date value. I would strongly discourage you from doing this, but if that data exists in your database and you have to work with it, there are two options for handling it in C#:

1. Set ConvertZeroDateTime=true; in your connection string. When this option is set, MySqlConnector will convert 0000-00-00 to DateTime.MinValue.
2. Set AllowZeroDateTime=true; in your connection string. When this option is set, all DATE values returned from MySqlDataReader.GetValue will be returned as MySqlDateTime objects that support storing a DateTime or the special 0000-00-00 value; use IsValidDateTime to tell them apart.

Note that in both cases, you should use GetValue or Get(MySql)DateTime to retrieve the value from the database, instead of calling GetString and parsing it yourself.