I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

Print | posted on Wednesday, March 14, 2007 7:21 PM | Filed Under [ .Net SQL Server ]

Feedback

Gravatar

# 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.
3/14/2007 8:39 PM | Scott Elkin
Gravatar

# 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
3/15/2007 12:08 AM | James Curran
Gravatar

# 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
3/15/2007 2:36 AM | Denis the SQL Menace
Gravatar

# 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!
3/15/2007 11:17 PM | Stephen Moore
Gravatar

# 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... :)
3/16/2007 10:18 AM | Mladen
Gravatar

# 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'.

3/16/2007 2:23 PM | Jon
Gravatar

# re: I don't like having Nulls in DateTime columns

Another vote for nulls - if there is no date then there is no date.
There is also a .net nullable types that helps you dealing with nulls.
3/18/2007 2:38 PM | Miha Markic
Gravatar

# re: I don't like having Nulls in DateTime columns

I am with you on this one Mladen.

NULLs suck.. you all know it!
3/18/2007 9:54 PM | DavidM
Gravatar

# 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)
3/19/2007 12:17 PM | Chas Long
Gravatar

# 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>
3/19/2007 3:20 PM | Steve Sturgis
Gravatar

# 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
3/27/2007 12:39 PM | kimzander
Gravatar

# 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.
3/27/2007 1:27 PM | Mladen
Gravatar

# 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?
4/12/2007 1:45 PM | Peter Larsson
Gravatar

# re: I don't like having Nulls in DateTime columns

simply wrong. that's all i can say.
4/12/2007 3:44 PM | Mladen
Gravatar

# 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...
4/20/2007 5:53 AM | Marc Brooks
Gravatar

# 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?

4/20/2007 6:38 PM | Jeff
Gravatar

# re: I don't like having Nulls in DateTime columns

are you subtley calling me a poor programmer, jeff? :)))
4/20/2007 6:53 PM | Mladen
Gravatar

# 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.
4/20/2007 8:50 PM | mcgurk
Gravatar

# 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.
4/23/2007 3:33 PM | Jeff
Gravatar

# 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 :))
4/23/2007 3:36 PM | Mladen
Gravatar

# 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
4/24/2007 9:18 AM | Damien Guard
Gravatar

# 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.
4/26/2007 3:56 PM | Jakub Keller
Gravatar

# re: I don't like having Nulls in DateTime columns

good UNDERSTANDING
4/29/2008 11:15 AM | happy
Gravatar

# 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
5/1/2008 12:54 AM | jason
Gravatar

# 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.
5/25/2008 6:52 AM | Jakub Keller
Gravatar

# 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.
2/10/2009 10:56 PM | brent
Gravatar

# 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.
2/10/2009 11:09 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET