SqlDateTime overflow, but I want a NULL or MinValue

Question

On this project, I am tracking when something is created, edited, and finally processed. I have three DateTime fields setup for this. The code below is for when I create the record.

newsArchive.CreateDateTime = DateTime.Now;
newsArchive.ModifyDateTime = DateTime.MinValue;
newsArchive.SendDateTime = DateTime.MinValue;
naRepository.Add(newsArchive);
naRepository.Save();

I am getting "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." when I try to add it. I know I can't send a NULL. How would you handle this?

I am using Linq2SQL to process this.

When you say you know you can't send a null - do you mean you can't use newsArchive.SendDateTime = SqlDateTime.Null; or do you mean you can't use newsArchive.SendDateTime = null; ? Your subject indicates NULL would be acceptable. — Robert Horvick
– Robert Horvick, Commented Jan 12, 2011 at 20:15

KeithS · Accepted Answer · 2011-01-12 20:18:42Z

3

Why can't you send a null? AFAIK, Linq2SQL supports nullable value types; you just need the column in the DB to be nullable or L2S won't project the DAO with the nullable type.

You could use (DateTime)(SqlDateTime.MinValue) instead of DateTime.MinValue. This will use the minimum SQL-compatible datetime value, explicitly cast to DateTime.

Though both SqlDateTime and DateTime actually store time as a UInt64 "Ticks" value, the difference between the two types is that SQL Server uses a different epoch (value for zero) and a different resolution (what 1 "tick" represents in fractions of a second) than the CLR's DateTime.

answered Jan 12, 2011 at 20:18

KeithS

71.9k24 gold badges118 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Wills Over a year ago

Weird I thought that errored out before. It works now. I can use = null and have it work fine. The DateTime on server allows nulls.

Josh · Accepted Answer · 2011-01-12 20:17:14Z

2

Which version of SQL Server are you using? If you are using SQL 2008 (or 2008 R2) you can use the datetime2 data type which can support the full range of the .NET DateTime type.

Otherwise, use NULL (your model will need to have a property of type Nullable<DateTime>) or live within the limitations of the data type and DateTime.MinValue exceeds the limit of the SQL datetime type.

answered Jan 12, 2011 at 20:17

Josh

69.4k15 gold badges149 silver badges158 bronze badges

Comments

mmmmmm · Accepted Answer · 2011-01-12 20:11:56Z

0

Create your own minimum date - a Date variable with value 1/1/1753 12:00:00 AM and use that instead of DateTime.MinValue

answered Jan 12, 2011 at 20:11

mmmmmm

32.8k28 gold badges92 silver badges124 bronze badges

Comments

Jonas Høgh · Accepted Answer · 2011-01-12 20:17:41Z

0

The datetime2 datatype allows dates as early as year 1. You can also use DateTime? to allow null dates in your .net code

answered Jan 12, 2011 at 20:17

Jonas Høgh

10.9k1 gold badge29 silver badges49 bronze badges

Collectives™ on Stack Overflow

SqlDateTime overflow, but I want a NULL or MinValue

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related