[Solved] GetDateTime delivers 01.01.0001 00:00:00

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
uglykidkap
Posts: 2
Joined: Mon 18 Nov 2013 09:28

[Solved] GetDateTime delivers 01.01.0001 00:00:00

Post by uglykidkap » Mon 18 Nov 2013 09:53

Hi Guys,

I am using the current version of dotConnect for MySQL. (8.1.36.0)

In my database I got a datetime column with the value: 0000-00-00 00:00:00

If I query this value from my .Net application I get the value: 01.01.0001 00:00:00

Code: Select all

                MySqlConnection Connection = new MySqlConnection();
                Connection.UserId = "test";
                Connection.Password = "test";
                Connection.Host = "test";
                Connection.Database = "testdb";
                Connection.Open();


                if (Connection.State == System.Data.ConnectionState.Open)
                {
                    string query = string.Format("select sessions_enddate from sessions where sessions.sessions_id = 48;");

                    MySqlCommand myCommand = new MySqlCommand(query, Connection);

                    MySqlDataReader myReader = myCommand.ExecuteReader();

                    // Always call Read before accessing data. 
                    while (myReader.Read())
                    {
                        // returns 01.01.0001 00:00:00
                        Console.WriteLine(myReader["sessions_enddate"].ToString());
                        
                        // returns 01.01.0001 00:00:00
                        DateTime EdnDate = myReader.GetDateTime(myReader.GetOrdinal("sessions_enddate"));
                    }

                    // always call Close when done reading. 
                    myReader.Close();

                    Console.ReadKey();
                }

Why?

Best regards,

Philipp
Last edited by uglykidkap on Wed 20 Nov 2013 09:18, edited 1 time in total.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: GetDateTime delivers 01.01.0001 00:00:00

Post by Pinturiccio » Mon 18 Nov 2013 15:26

uglykidkap wrote:In my database I got a datetime column with the value: 0000-00-00 00:00:00
According to MySQL documentation '0000-00-00 00:00:00' is the result of automatic conversion of an illegal DATETIME value to the “zero” value. And the supported range for DATETIME type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. For more information, please refer to http://dev.mysql.com/doc/refman/5.0/en/datetime.html
uglykidkap wrote:If I query this value from my .Net application I get the value: 01.01.0001 00:00:00
The minimal value for .NET Framework DateTime type is 00:00:00.0000000, January 1, 0001. For more information, please refer to http://msdn.microsoft.com/en-us/library ... 10%29.aspx
uglykidkap wrote:Why?
When reading an invalid DATETIME value, there are two possible ways. The first is to throw an exception. The second is to replace the invalid value with some other value. We decided to choose the second option and to replace invalid DATETIME values with the minimal value of the DateTime object.

uglykidkap
Posts: 2
Joined: Mon 18 Nov 2013 09:28

Re: GetDateTime delivers 01.01.0001 00:00:00

Post by uglykidkap » Wed 20 Nov 2013 09:16

Hi Pinturiccio,

good to know, thank you for your detailed explanation!

Best regards,

Philipp

Post Reply