Rune,
I've got some similar situations in a database that I'm working with and found that the alternative seems to be just as bad. We've created functions to get and set min and max dates and usually will default a date to the f_MinDate() just so we don't have NULLs in the database. But now you still have to check if the value is "MinDate" and if so leave it out of your comparison.
That usually has me writting code that looks like:
WHERE ( Table.Date = f_MinDate() OR Table.Date BETWEEN @StartDate AND @EndDate )
Not sure if that is much better? And it gets even uglier if you allow the start and end dates of the period in question to be allowed to be null. Don't really have an answer to the issue, seems like you're damned if you do and damned if you don't.
Rune Bivrin (9/24/2010)
jeff.mason (9/24/2010)
As far as NULLs go, I have to say NULL is a good thing for some DATE columns. If you have, say, a StartDate and an EndDate column, you know the StartDate when the row is created but you may not know the EndDate. If you insist on having no NULLs, then you have to make up some arbitrary date (usually 1/1/1900) to allow a row to be created. But then that row works on date logic for functions, et al. Simply letting a Date field be NULL in those cases where you lack knowledge makes life so much easier.Absolutely. Unless, of course, you find yourself writing a lot of code like
WHERE getdate() BETWEEN ISNULL(StartDate, '1900-01-01') AND ISNULL(EndDate, '9999-12-31')in which case the NULLs will quite possibly kill your performance. I have worked quite a bit with industrial insurance where there are periods on everything, and that gets old very quickly...
Unfortunately SQL Server lacks stuff like DATE.MinValue constants that would be a good compromise.
Not that I'm arguing that NULL is bad per se, just that it's good practice to question its merits in the design phase.