CODEWITHSUNDEEP
c#mysql
4 Leave Cover
4 Leave Cover

Reputation: 1276

String was not recognized as a valid datetime, source date is 0000-00-00 00:00:000

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

enter image description here

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

Upvotes: 0

Views: 3928

Answers (3)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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();

Upvotes: 1

Stefan Illner
Stefan Illner

Reputation: 179

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

Upvotes: 0

user10296025
user10296025

Reputation:

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

Upvotes: 0

Related Questions