Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

I don't like having Nulls in DateTime columns

I just hate having nulls in my DateTime columns. Having them always mean you also have to handle them in some way in your app.

The most common way is something like this:

public DateTime SomeDate
{
    get
    {
        if (dr["SomeDate"] == DBNull.Value)
            return DateTime.MinValue;
        else
            return (DateTime)dr["SomeDate"];
    }
    set
    {
        if (value == DateTime.MinValue)
            dr["SomeDate"] = DBNull.Value;
        else
            dr["SomeDate"] = value;
    }
}

Of course you could write improvements in form of attributes or something similar to reduce the repetetive coding but in the end this had to be implemented.

Yes, having nulls saves space, but disk space is cheap :)

 

That's why i use the default value for my DateTime columns it's max value which is '99991231 23:59:59:997'. you can use min value if you like it more :)

i use this function for my default values 

CREATE FUNCTION dbo.DateTimeMaxValue()
RETURNS DATETIME
AS
BEGIN
    RETURN CONVERT(DATETIME, '99991231 23:59:59:997')
END
GO

and what would MaxValue be without MinValue 

CREATE FUNCTION dbo.DateTimeMinValue()
RETURNS DATETIME
AS
BEGIN
    RETURN CONVERT(DATETIME, '17530101')
END
GO

i don't know why sql server doesn't already have these functions. If it, by any chance, does have them then i'd love to know about them.

 

However this again presents some problems since SQL datetime and .Net datetime have different ranges. 

  Min Date Max Date
.Net 0001-01-01 00:00:00.000 9999-12-31 23:59:59.999
SQL Server 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997

 

       If you have SQL Server 2005 and would like to extend your DateTime range to match .Net's read my post here.

 

So we see that you can't simply do

if (.net datetime maxvalue == sql server datetime maxvalue)

 

BUT…

In .Net 2.0 we got the System.Data.SqlTypes namespace which gives us native SQL datatypes in .net.

So we also get SqlDateTime, which has a MaxDate and MinDate properties.

and

SqlDateTime.MaxValue = 9999-12-31 23:59:59.997
SqlDateTime.MinValue = 1753-01-01 00:00:00.000

 

So you can easily find which dates have the max/min value and handle those anyway you like.

 

At least something is a bit simplified.

Now i'd just love to be able to do dr["SomeDate"] = SqlDateTime.Null but despite SqlDateTime.Null exists you can't assign it like that.

You still have to use DbNull.Value.

 

kick it on DotNetKicks.com

Legacy Comments


Scott Elkin
2007-03-14
re: I don't like having Nulls in DateTime columns
I wrote a SmartDate class that gracefully handled nulls. On it, I have DBValue property that looks if it is null and returns DBNull to the database.

My IsEmpty property does a "m_Date.Equals(DateTime.MinValue)" check.

James Curran
2007-03-15
re: I don't like having Nulls in DateTime columns
Well, since DateTime are, beneath the covers, just floating-point numbers, you may want to save yourself a bit of processing:
CREATE FUNCTION dbo.DateTimeMaxValue()
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, 2958463.99999999)
END
GO

CREATE FUNCTION dbo.DateTimeMinValue()
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, -53690.0)
END

Denis the SQL Menace
2007-03-15
re: I don't like having Nulls in DateTime columns
>>Well, since DateTime are, beneath the covers, just floating-point numbers

Floating points? Internally dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second., see more detail here: http://sqlservercode.blogspot.com/2006/11/how-are-dates-stored-in-sql-server.html

Denis

Stephen Moore
2007-03-15
re: I don't like having Nulls in DateTime columns
I also disagree with you on this, although I enjoy reading your posts and usually agree with you. We have an app that uses 1/1/1753 instead of using a null, and it's a pain in every report, query from Excel, etc to have to hide the values from users who invariably ask questions about it. Plus the whole point of a null value is to represent unknowns. Picking a min or max datetime value says you know what it is. I also don't really understand the distinction between null datetime values and other data types which you would have to deal with as well. But that could be because I'm not much of a developer anymore. Anyway, thanks for your blog!

Mladen
2007-03-16
re: I don't like having Nulls in DateTime columns
well... i guess i have to get bitten in the ass a few times to see the light.
haven't been yet... :)

Jon
2007-03-16
re: I don't like having Nulls in DateTime columns
I tried that once (using some sort of default date) and it bite me more than helped me. Especially when you are designing reports in RS...you end up having to deal with this ugly looking date, and people always question it. I know theres tons of wars in the db community of why you should or shouldn't use NULLS. I prefer them if things are really unknown, especially with dates. We have an application that uses a lot of target dates and at times we just dont know when stuff is going to be completed. Instead of displaying 1/1/1900 I'd rather display a string representing the null value, such as 'N/A' or 'data not available'.


DavidM
2007-03-18
re: I don't like having Nulls in DateTime columns
I am with you on this one Mladen.

NULLs suck.. you all know it!

Chas Long
2007-03-19
re: I don't like having Nulls in DateTime columns
Since many database implementations have come without nulls and worked perfectly well, I've a lot of sympathy with Mladen's view; especially if one needs to stray into VBA where null handling is problematic. However I'm more interested in .Nets foray into the Julian calendar era; I always assumed 1/1/1753 was chosen as the next convenient point in time following adoption of the Gregorian calendar in September 1752. That adoption date is good for the UK and its then colony the USA; not so good for anywhere else much - http://www.nottingham.ac.uk/mss/online/skills/dating/julian-gregorian.phtml. I'd be interested to know if that's handled and if so how; or does Catholic Europe get incorrect values for medieval and early modern dates (just as Russia does for 1/1/1753 until the Communists adopted the same calendar as the rest of the developed world after the 1917 revolution)

Steve Sturgis
2007-03-19
re: I don't like having Nulls in DateTime columns
I agree with you in one circumstance--effective and expiration dates.
We have a table that has an effective date and an expiration date. It makes it much easier to query a date value between two dates (either using BETWEEN or the SQL rendered [date] >= [effective] AND [date] <= [expired]) than it does to query for a NULL date in either the effective or expiration.
<value of comment>
2 cents
</value of comment>

kimzander
2007-03-27
re: I don't like having Nulls in DateTime columns
Good post (and comments),

I got confused though at the end when you wanted to set a dr["dateTime"] column to NULL.

You obviously don't like nulls but the statement seems to create one? Am I wrong?

Thanks
--Kim

Mladen
2007-03-27
re: I don't like having Nulls in DateTime columns
yes id did.
because let's face it... it really does depend on the application.
so if you do have nulls it would be a lot easier if null, DbNull.Value and SqlDateTime.Null would all be the same.
there's probably a reason for null and DbNull.Value not being equal but i don't see why
SqlDateTime.Null and DbNull.Value aren't equal.

Peter Larsson
2007-04-12
re: I don't like having Nulls in DateTime columns
But that is not a database issue. It is a presentation issue. And some programming languages may be more or less able to handle NULLs.

I was recently suggested to add a BIT column to the table. 0 if the date data is invalid and 1 if the date data is valid. This way you can have any value in the date column. Let us have the BIT column decide if the date data is usable.

What do you think of that approach?

Mladen
2007-04-12
re: I don't like having Nulls in DateTime columns
simply wrong. that's all i can say.

Marc Brooks
2007-04-20
re: I don't like having Nulls in DateTime columns
I agree COMPLETELY that Dates should not be null (and I'll go so far as to state that very few if anything in a database should be NULL). I use the sentinal date approach and it makes queries so much easier to write, and guarantees that indexes can be used to satisfy the criteria of WHERE clause.

As for those that whine about how it makes reports harder to write, what's wrong with doing NULLIF(person.birthday, DateTimeMaxValue()) for the VIEW or flattened table-set that you're driving your report from. You ARE driving your report from a VIEW aren't you? Surely you wouldn't bind a report to a raw table's schema, right?

The absolute BIGGEST reason to not use NULLs for dates is that _people will forget_ when doing queries and there's simply NO WAY for you code to catch the error. If a column is reasonably defaulted (once, during table/class definition), you ALWAYS get simple queries. If you have a nullable column, on the other hand, EVERY SINGLE query needs to be examined to determine what the "don't knows" should mean...

My way, where a BirthDay defaults to DateTimeMinValue and a DeathDay defaults to DateTimeMaxValue means that if we insert a row into the Person table with either unspecified, we get the right answer for the most obvious query you could write to get currently live people:

SELECT * FROM Person WHERE GetUtcDate() BETWEEN BirthDay AND DeathDay;

For a nullable query, you end up with either:

SELECT * FROM Person WHERE ((BirthDay IS NULL OR BirthDay <= GetUtcDate()) AND (DeathDay IS NULL OR DeathDay >= GetUtcDate()))

Which has a horrid query plan due to the NULLs or just as ugly, and non-SARGable:

SELECT * FROM Person WHERE (ISNULL(BirthDay, DateTimeMinValue()) <= GetUtcDate() AND (ISNULL(DeathDay, DateTimeMaxValue()) >= GetUtcDate())

I cover a lot of this over at my blog...

Jeff
2007-04-20
re: I don't like having Nulls in DateTime columns
Marc -- what happens if I want everyone who has died since 1/1/1980? what does this return with DateTime.Max as the default and standard "null" deathdate?

select * from yourtable where DeathDate >= '1/1/1980'

??

This statement :

>>The absolute BIGGEST reason to not use NULLs for dates is that _people will forget_ when doing queries and there's simply NO WAY for you code to catch the error.

makes no sense for your argument because it applies to the situation above as well !! So, it completely negates your "absolute biggest reason" !

What happens when I "forget" that some columns have DateTime.Min and other's dateTime.Max (as in the above?) the same thing, or maybe worse!

Nulls are standard. they are expected. Any good programmer should know of them intuitively. Any good programmer should NOT be expected to know that sometimes you are picking certain magic dates to mean other things in different cases! That is no standard behavior or a standard practice. If a poor programmer "forgets" how to use NULLS, then he is a poor programmer! A poor programmer also doesn't know how to use classes and methods properly, so should we aim to write our code completely free of any OOP principles so that "bad programmers" will not get confused?


Mladen
2007-04-20
re: I don't like having Nulls in DateTime columns
are you subtley calling me a poor programmer, jeff? :)))

mcgurk
2007-04-20
re: I don't like having Nulls in DateTime columns
Apparently there are two kinds of people: Nulls everywhere, and nulls only for optional foreign keys. I'm more of the second, but not a purist. Where nulls make sense, and won't hurt query performance/ease of maintenance, go buck wild.
But I utterly despise nulls in places like bits. A bit is 1 or 0, not 1 0 or null. If you need to keep track of three states, you don't need a bit flag. Use a tinyint or something else.

Jeff
2007-04-23
re: I don't like having Nulls in DateTime columns
Of course not Mladen !!!! We all have opinions, of course -- it's just that mine is right and yours is wrong, that's all !! :)

(kidding of course)

my last post was in response to Marc's comment about his "biggest reason" comment, which didn't make sense as demonstrated.

Mladen
2007-04-23
re: I don't like having Nulls in DateTime columns
i know, i know... i just had to take the opportunity to mess a bit with you, jeff :))

Damien Guard
2007-04-24
re: I don't like having Nulls in DateTime columns
Some dates logically make sense to be nullable, others do not. If you chose correctly then it should not be a problem at all. Using sentinel values - in other words incorrect data - into the database is just plain wrong and lazy.

C# handles null's just fine since .NET 2.0 with nullable types and there is absolutely no need to resort to sentinel values in this layer whatsoever.

public ?DateTime SomeDate
{
get {
return (dr["SomeDate"] == DBNull.Value) ? null : (DateTime)dr["SomeDate"];
}
set {
dr["SomeDate"] = (value == null) ? DBNull.Value : value;
}
}

The claims about "storage is cheap" is nothing to do with it. Sure you could have a bit field to go with it but then what does the date mean when the bit is off? What will stop people using this date regardless of the bit?

The same can be also said for other programmers and report writers where one of the dates logically forms part of a range, e.g. maturity date for certain interest-bearing accounts.

If there are a few people that use your databases but don't get null's then sit down with them and show them the joins and selects to use them effectively.

[)amien

Jakub Keller
2007-04-26
re: I don't like having Nulls in DateTime columns
I disagree with you as well.
The best approach, hands down, to specify a field's value is "nothing" or non-existent is to declare it NULL.
It just doesn't make much sense to either use the min 1753-01-01, and worse yet is to use an arbitrary date.
It's counter-intuitive, not to mention much more difficult to maintain and use on the SQL-side.
Using NULL in that respect applies to textual content (i.e. varchar) as well.

happy
2008-04-29
re: I don't like having Nulls in DateTime columns
good UNDERSTANDING

jason
2008-05-01
i heartily concur
I know it's a year later, but I have to agree with you.

I find NULLS to be evil because they are typically associated with three variable logic in most RDMS's. Trying to warp the rest of the 2VL computer science world around it is a horrendous nightmare.

Supplementally, why MS didn't have a greater degree of agreement between DateTime and SqlDateTime is beyond me.

Jason

Jakub Keller
2008-05-25
re: I don't like having Nulls in DateTime columns
Totally wrong on all accounts. NULL values just make way too much sense for a DateTime, which is why adding concepts like nullables to .NET languages also makes sense. There can be many instances where a DateTime does not include a value. One example is, a ModifiedDate field. A ModifiedDate field initially doesn't have any value. So what would you do? Are you telling me you would seriously insert some kind of arbitrary value to avoid a NULL? That's simply ridiculous. There is so much room for error if you put some kind of default value rather than put a NULL. NULL is a definite value that you can count on, a default value is not.

brent
2009-02-10
re: I don't like having Nulls in DateTime columns
There is one important point made by Marc that no one has refuted, and I am curious to know if anyone can say ought against it.

the query:
select *
from table1
where somedate between startdate and enddate;

is certainly easier to maintain, easier to understand, and significantly faster that the query:

select *
from table1
where (somedate >= startdate or startdate is null)
and (somedate <= enddate or endate is null);

or, is the second query also fast if the proper indexes are in place?
does SQL use short circut boolean logic, so it could be faster to weed out the nulls?

if it does use short circut logic, then would it be faster to say...
select *
from table1
where (startdate is null or somedate >= startdate)
and (endate is null or somedate <= enddate);

I still think the first query is much more usable. In fact, I agree with marc's entire post 100%, everyone who disagreed offered very little by way of decent arugements other than personal preference, and he refuted several pro null concerns quite elegantly.

But I am not here to comment on the code, I'm here trying to decide if my DB will be faster with queries where I merge tables based on a date field in one table being inside a range of days in another table if I use null rather than DateTime.Max. I lean toward DateTime.Max.

Thanks for the tip about using NullIf() to make writing reports cleaner.

Mladen
2009-02-10
re: I don't like having Nulls in DateTime columns
sql server does not short circuit where conditions:
http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx

put nulls where you think it makes sense.