DateTime.MaxValue Metamorphosis

Suppose you need to represent a date range. If you want dates on each boundary to be infinite, you might want to use DateTime.MaxValue and DateTime.MinValue, right? Why reinvent the wheel if the .NET Framework has all you need. Not so fast! If you want to store this range in a database and read it back, you'll see that MaxValue != MaxValue.

Something interesting happens if you store DateTime.MaxValue in the database and then read it out: the two are different! A trivial comparison of dates will fail. When I first ran into this issue, I thought it was against reason.

By design, DateTime.MaxValue renders Friday, December 31, 9999 11:59:59.999 PM. ADO.NET returns Friday, December 31, 9999 11:59:59.997 PM from the database. If you compare the two dates carefully, you'll see they are 3 milliseconds apart! In real life I'd deposit 3 Lincolns in a penny jar at my local 7-Eleven, but in code 3 milliseconds do matter.

Interestingly enough, the second date is what SqlDateTime.MaxValue returns. I'm not sure what happens, but it seems that the date is mapped from DateTime into SqlDateTime along the way. Somebody, please correct me if I'm wrong.

I found only one post at Google Newsgroups about this issue. Someone suggested to use SqlDateTime instead wherever date manipulation took place. I'm not comfortable with that.

1. SqlDateTime belongs to the domain of a data access tier. I don't want to drag it around a presentation tier.
2. if I trust ADO.NET to store a date, I'd expect the same date to come back!

In light of all said here's my advice: make up your own "infinite" date and stick to it. Something as ridiculous as January 1, 2200, should do.

By that time we'll bomb the hell out of each other so you don't have to worry about it. Also, you won't have to concern yourself with the Y3K problem for a long time.

We did exactly as you advise. We implemented two sets of functions to convert dates coming and going from the database (minimum date is 1/1/1900 in SQL Server 2k. The horrendous thing is maintaining it at each layer its needed, but if we know that outside of the database, we expect DateTime.MinValue and in the database its 1/1/1900, we're golden.

I hear you. I had to write a small utility class (only static methods) to handle my own "infinite" dates in the past and future to avoid more surprises.

BTW, smalldatetime has the lower cap of 1/1/1900. The other one, datetime, goes all the way back to 1/1/1753.

The problem with picking your own DateTime Max value is that then someone else comes along and it takes them 20 to go through the code to find what is going on and your code eventually ends up on thedailywtf.com. Hehe.

I suppose you could always just compare the date portion without time. If the date portion (without time) retrieved from the database = the date portion of DateTime.MaxDate. Seems like a whole lot of extra overhead all for a difference of 3 crappy miliseconds.

That just sucks.

I didn't want my posterity to have trouble figuring out my own custom "infinite" dates, so I created a utility class where I defined them. Whenever I need to reference, say, the "infinite" date in the past I do "DateHelper.MinimumPastDate".

I also added a static method to determine if a date in question is any of the "infinite" dates in the future or in the past:

public static bool IsOpenDate (DateTime date)
{
return (date == MaximumFutureDate ||
date == MinimumPastDate);
}

A good way (which eliminates defining an arbatory "max" or "open" date is to always pass dates going between Business Logic (.NET) and Data Layer (sql) via a simple "filter" which converts .NET "max" to "sql" max and vice versa.

This way you can always take advantage of ".. == MaxValue" in both layers.

The filter could also do the conversion of MinValue at the same time.
i.e.
SqlDateTime BLLToDAL(DateTime dt)
{
if(dt == DateTime.MaxValue) return SqlDateTime.MaxValue;
if(dt == DateTime.MinValue) return SqlDateTime.MinValue;
return d;
}

I looked up SqlDateTime and DateTime in the .NET docs.

Both can store dates up to Dec 31, 9999. But here is the difference:

DateTime: "Time values are measured in 100-nanosecond units called ticks."

SqlDateTime: "Represents the date and time data...to an accuracy of 3.33 milliseconds."

Since DateTime is stored with more precision, converting it to SqlDateTime and back again could result in tiny rounding errors, even when dealing with dates close to the present. This usually doesn't matter, since virtually all other date constants will be nice "round" ones like "December 25, 2010". If we added 100 nanoseconds to that date, stored it in the DB and then retrieved it, they would probably not match.

I had a slightly different "problem"

I retrieved a DateTime from a DB in VB.Net and saved it in a
DateTime VB variable. NO writes were made to that variable,
and reads showed the correct value.

Later, I sent that unchanged value back via a stored procedure
parameter. SQL Server choked and said it's out of range!

It is nowhere near either end of the VB range, which is a
subset of the SQL range....

thanks for the share