CODEWITHSUNDEEP
c#sqldatetimeoledbdatareader
user4615254
user4615254

Reputation:

DateTime null values

I have the following code that is supposed to check that a value in a data reader is not null. If it is then the method should return a null value or null DateTime for use later. 

private static DateTime safeGetDateTime(OleDbDataReader dr, int idx)
    {
        if (!dr.IsDBNull(idx))
            return dr.GetDateTime(idx);

        else return DateTime.MinValue; 
    }

I have tried just returning null but as the methods return type is "DateTime", this did not work. I then tried to return DateTime.MinValue if a null is picked up by the datareader. This has given me the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Any help is appreciated.

Upvotes: 1

Views: 2809

Answers (4)

Pikoh
Pikoh

Reputation: 7713

The error must come from you assigning DateTime.MinValue to a SqlDateTime parameter.

DateTime.MinValue:

The value of this constant is equivalent to 00:00:00.0000000, January 1, 0001.

SqlDateTime.MinValue

The minimum valid date for a SqlDateTime structure is January 1, 1753.

So if you are going to use the return of this method in a SqlDateTime parameter, you can try returning SqlDateTime.MinValue:

return (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;

Alternatively, you can try using DateTime? as the returning type, so you can return null

Upvotes: 2

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59533

You can keep safeGetDateTime just as it is, but when you write the value back to the database, you should check if it is the DateTime.MinValue. If it is, then you should write a null value to the database.

if (value == DateTime.MinValue)
{
    comm.Parameters.AddWithvalue("@mydate", null);
}
else 
{
    comm.Parameters.AddWithValue("@mydate", value);
}

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460268

Let the method return a Nullable<DateTime>, then you can return null:

private static DateTime? safeGetDateTime(OleDbDataReader dr, int idx)
{
    if (!dr.IsDBNull(idx))
        return dr.GetDateTime(idx);
    else 
        return null; 
}

You still have to check the return value, your error suggests that you are using this value in another sql query as parameter, you have to assign DBNull.Value in the null-case. 

DateTime? date = safeGetDateTime(reader, columnOrdinal);
yourSqlParameter.Value = date.HasValue ? (object)date.Value : DBNull.Value;

Upvotes: 3

ilya korover
ilya korover

Reputation: 230

You get this error because of DateTime.MinValue differeneces in CLR and SQL server.

I suggest you to use Nullable Type

Upvotes: 4

Related Questions