Question 276985 in Mysql Question posted in
The official MySQL documentation can be found here.

How i make a good parse of a Datetime in C# for a MySQL Column?

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

this error in my program

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:

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:

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    I think you can try TryParse or TryParseExact method and if conversion is not successful use DateTime.MinValue or process it differently.

    var successfullyConverted = DateTime.TryParse("0000-00-00", out DateTime convertedDateTime);
var successfullyConverted = DateTime.TryParseExact("0000-00-00","yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime convertedDateTime);
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search