CODEWITHSUNDEEP
c#sql-serverdatetimedatetime-conversion
Mystus
Mystus

Reputation: 459

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

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!

Upvotes: 1

Views: 1116

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1499740

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)
{
    ...
}

Upvotes: 4

Phoenix_uy
Phoenix_uy

Reputation: 3294

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...
        }

Upvotes: 0

Related Questions