Casting SQL Date Value using Convert.ToDateTime in C# is failing

Question

To start, the error I'm getting is as follows:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

What I've done is returned a set of data from a SQL database into a datatable, from there I'm running an if statement on the c# code side to determine if one date is greater than the other. This has worked on numerous deployments, however a specific deployment on a new server is resulting in the above error.

Here is my code:

if
(
    Convert.ToDateTime(ds.Tables[0].Rows[0]["StartDate"].ToString()).Date >
    Convert.ToDateTime(ds.Tables[0].Rows[0]["OriginalStartDate"].ToString()).Date
)
{
   ... do stuff ...
}

My understanding is that the data inside the column, converted to a string, is failing when trying to execute the Convert.ToDateTime()

The perplexing issue however is that this code has worked on other servers and this issue is isolated. So I get that it could be unique to the server however I don't know where to look.

Changing any regional settings, etc., is unfortunately not an option so I'm trying to find a way around this!

Any help is appreciated!

Answer 1

Basically, you shouldn't be converting the values to strings and back in the first place. Wherever possible, avoid string conversions unless that's an essential part of what you're trying to do. In your case, you're really interested in DateTime values. They're DateTime in the database, and you want them as DateTime values in your code, so why introduce strings?

You should be able to use:

DataRow row = ds.Tables[0].Rows[0];
DateTime startDate = (DateTime) row["StartDate"];
DateTime originalStartDate = (DateTime) row["OriginalStartDate"];
if (startDate.Date > originalStartDate.Date)
{
    ...
}

Answer 2

Maybe you can try this?

        if (Convert.ToDateTime(ds.Tables[0].Rows[0]["StartDate"].ToString("yyyyMMdd HH:mm:ss")).Date > Convert.ToDateTime(ds.Tables[0].Rows[0]["OriginalStartDate"].ToString("yyyyMMdd HH:mm:ss")).Date) 
        {
                ...do stuff...
        }