Pfthb

UPDATE

Apparently it was not caused by the 0000-00-00 00:00:000, the program error out when the value was 2016-04-21 00:00:00.000 Any idea what could be the cause?

I have a VS C# program that will SELECT from MSSQL then INSERT/ON DUPLICATE UPDATE into MySQL database. I have a particular row which the datetime is NULL, my MSSQL query and result are:

Query

SELECT UserID,LastPasswordDate,
CASE WHEN LastPasswordDate IS NULL THEN '0000-00-00 00:00:00:000' 
 ELSE convert(varchar, LastPasswordDate, 121) END as LastPasswordDate2 from users
 order by LastPasswordDate

Result

C# code

string LastPasswordDate = row["LastPasswordDate"].ToString(); // Or
//DateTime LastPasswordDate = DateTime.ParseExact(row["LastPasswordDate"].ToString(), "yyyy-MM-dd HH:mm:ss:fff", null);

insertUserCommand.Parameters.AddWithValue("@LastPasswordDate", LastPasswordDate);
insertUserCommand.ExecuteNonQuery();
insertUserCommand.Parameters.Clear();
tran.Commit();

I tried to use C# conversion but keep getting same error message as per title mentioned

The first thing you should know is that datetime data type in MySQL has minimum value of 1000-01-01 00:00:00.000, not 0000-00-00 00:00:00.000 which used as "zero" value display when using datetime conversion for invalid date. Second, the DateTime.MinValue has minimum value of 0001-01-01 00:00:00.000, not suitable for conversion against MySQL's "zero" value as mentioned before.

If the target column in MySQL DB has nullable datetime data type, you should use TryParseExact() and use DBNull.Value for assign null value when the "zero" date cannot be parsed:

DateTime date;
DateTime? LastPasswordDate;

if (DateTime.TryParseExact(row["LastPasswordDate"].ToString(), out date))

 LastPasswordDate = date;

else

 LastPasswordDate = null;


insertUserCommand.Parameters.AddWithValue("@LastPasswordDate", (object)LastPasswordDate ?? DBNull.Value);
insertUserCommand.ExecuteNonQuery();

But in my thoughts it's better to return null value from T-SQL query and check it with Convert.IsDBNull(), then use DBNull.Value for assign null values into database column:

DateTime? LastPasswordDate = !Convert.IsDBNull(row["LastPasswordDate"]) ? DateTime.ParseExact(row["LastPasswordDate"].ToString(), "yyyy-MM-dd HH:mm:ss:fff", null) : null;

insertUserCommand.Parameters.AddWithValue("@LastPasswordDate", (object)LastPasswordDate ?? DBNull.Value);
insertUserCommand.ExecuteNonQuery();

the problem is your string '0000-00-00 00:00:00:000' that you are using for the check cannot be converted to a valid date time in code even though it can be saved like that in the database. best solution is to set a default value of null in the database and look for that

In .Net, DateTime.MinValue is 1/1/0001 12:00:00 AM, so 0000-00-00 00:00:00 is outside of the range of valid DateTime values.

see DateTime.MinValue