DateTime null values

Question

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.

If you notice that the error is from `SqlDateTime` not `DateTime`, I think you'll find that it's throwing the exception on the `return dr.GetDateTime(idx);` line, not the `return DateTime.MinValue;` line. — Michael L., Mar 22 '17 at 15:46
@MichaelL. That seems unlikely, as the value in the database is already a valid SQL date. I suspect that the OP has misunderstood the location of the error, and it is actually when the value is being written to the database, rather than when it is being read from the database. — Jeffrey L Whitledge, Mar 22 '17 at 16:01
Are you sure this is the location of the error? The error is likely happening when the value is being written to the database (though your previous null-conversion error would have happened here, before you changed the code). — Jeffrey L Whitledge, Mar 22 '17 at 16:02

score 4 · Answer 1 · answered Mar 22 '17 at 15:45

4

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

I suggest you to use Nullable Type

answered Mar 22 '17 at 15:45

ilya korover

230
1
3

score 3 · Answer 2 · answered Mar 22 '17 at 15:52

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;

Pikoh · Answer 3 · 2017-03-22T16:32:34.970

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

score 0 · Answer 4 · answered Mar 22 '17 at 15:57

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

DateTime null values

4 Answers4