Date Fields - To Null or Not to Null

I am new to MS-SQL and I am doing some testing with converting a few of our current tables from Pervasive 2000i format. The first thing I run into is problems with data fields. We have a lot of date fields in our various tables that have no date (00/00/0000) which is correct. It could mean for instance that something has not happened yet (i.e. Posting Date).

Ok here is my question: Is it better not to allow Nulls in Date fields? If so, what is the best practice to have for the default (i.e. 01/01/1901) date?

We use Crystal Reports and a RAD tool to produce our many complex reports. So this will be a very important decision.

Thanks,

FrankMy vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||I agree... Go NULL all the way

Originally posted by blindman
My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||You have 3 options as I see it..

1) Allow Nulls
Pros: Quick
Cons: Requires 3 valued logic, not very indexable

2) Define and Document a "bogus" date as blindman called it
Pros: You actually have a value that can use an index
Cons: Documentation and logic

3) Project away the date field into a new table using a one-to-none or one-to-one constraint.
Pros: Nice and relational...
Cons: Requires an extra join.

Depending on what option you do go for, don't forget that you can setup views to help out ie: ViewValidDates (Good Dates) and ViewNonValidDates (NULLs, BogusDate etc...)|||definitely use nulls

byrmol, since when can you not index a column that has nulls?

or are you implying that the index is never used? i know i've created indexes on columns with nulls (i was on sql 7) but i never bothered to dig into the execution plan to see if the index was actually being used

the "default" date will get you into WAY more trouble than nulls

that is because the "default" date forces upon you a different "third-value logic" and therefore since you have to code for three values anyway, it is better to use nulls, since there are cases (certain sql constructions) where nulls are easier than having always to remember to add "where datefield <> 'xxxx-xx-xx'" to each and every query, which, i guarantee you, somebody (usually yourself) will forget to do

as for the third option, normalizing the date out into a separate table, just so there's no column in the main table with a null in it -- i know where you're coming from but i really don't think you get much from this, because although you can bring in the date with a join, imagine doing that for all the fields in a typical application which can go null, and suddenly you have to code twenty-seven-table-join monstrosities just to get an "original" row

rudy|||null sounds good to me....