DateTime Error: 0000-00-00 00:00:00

Developer
Nov 11, 2013 at 6:49 AM
Hi adrian,

Nice to be back here again, btw i just want to inform you about what i found which i think is a bug in Mysqlbackup.net, i have this table with datetime datatype which as far as i know does not accept strings like '12/12/13' if you are using the mysqlconnector only this format '2013-08-28 01:45:03'. but in my application i was using the mysql_query() function of php and i was able to insert 0000-00-00 00:00:00 value to its database i dont know how php did it but i have that value in my datetime datatype field. Now when i tried to backup the database using the mysqlbackup.net i get an error, probably because mysqlbackup did not was not able to handle the value
 else if (ob is System.DateTime)
                {
                    sbData.Append(String.Format("'{0}',", ((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", df)));
                }
Thanks,
Ivan
Coordinator
Nov 12, 2013 at 1:43 AM
This is not a bug. It is an expected behaviour. MySQL allow zero date time which means 0 year 0 month and 0 days, but in .NET world's concept, there is no such 0 year, 0 month and 0 day. The minimum possible date in .NET is 01-01-01. (Commonly occur when data type of "DATE" (not DATETIME) used in one of the table).

There are 2 ways to resolve this:

1st, by adding allowzerodatetime in the MySQL connection string (Recommended if you use MySqlBackup)

Example:
server=localhost;user=root;pwd=qwerty;database=test;allowzerodatetime=true;
2nd, by adding convertzerodatetime in MySQL connection string.

Example:
server=localhost;user=root;pwd=qwerty;database=test;convertzerodatetime=true
Developer
Nov 12, 2013 at 2:31 AM
Ok, thanks, btw when cheked on www.connectionstrings.com i didnot find that there is convertzerodatetime=true. Anyway ill try it.
Coordinator
Nov 12, 2013 at 3:15 AM
You may refer MySQL official documentation.
Here is a full list of available parameters for connection string used by .NET Connector:

http://dev.mysql.com/doc/refman/5.7/en/connector-net-connection-options.html