Date Best Practice NULL vs. Future Date

I wanted to get feedback on a design issue. We have data elements where a row has a begin/end date that allows us to "expire" rows or indicate they were valid for a specific date/time period.

When there is an absence of an end date, I leave the attribute NULL. I have worked, and currently do, where alternative views hold that you should provide an End Date far in the future. The rationale is that it is easier to use the BETWEEN operator and non technical people can understand it.

I disagree with the future-date design concept and believe that a NULL End Date is appropriate. A NULL indicates an absence of value and providing a value is a violation of business rules.

Does anyone have any feedback or suggestions?

I agree that from a purely logical design perspective, the data should be NULL.

But for practical reasons, it is much easier to code if you just put a max-distant-future-date in that column. I'm a long-time professional DBA, and an actual date also "helps" the optimizer generate a better query plan. Anything like an ISNULL() or any other function on a column drastically hurts the optimizer's plan generation.

As long as the 'future' date is defined to a single value and all processes adhere to that rule then it works. As soon as someone decides they want a different 'future' date then it becomes a huge nightmare.

If one person decides that 2199-12-31 is the future date to use - and someone else has decided to use 9999-12-31 and another person chooses 2031-12-31 - you now have to account for any/all possibilities of future dates.

If the table cannot have a future date at all, then you might not have a problem - but as soon as you allow future dates to be entered you have a significant issue to code for those possibilities. For instance, you want to expire certain rows next month - other rows the month following - you now have to code to exclude those pre-defined future dates that really mean end of time.

True, if you allow individual developers to just pick their own date(s), you could have chaos.

Instead, have a control value somewhere that everyone looks up and uses to set the "forever" date. That date should not be hard-coded anywhere.

Thank you all for your comments. I so appreciate the professional community and the time you take to help.


I like using the max date the date, datetime, or datetime2 data type supports. To me this logically represents a row that is valid now. Any other future date would indicate the row is valid until that date. That's the method MS used in adding the temporal tables feature in SQL 2016 (system versioned tables).

That is a fantastic idea.


I'll also add that I use the future date standard of '9999', which translate to 9999-01-01 when changed to a date/datetime temporal datatype. It leaves a little headroom for certain types of temporal calculations (9999-12-31 does not), is wicked easy for everyone to remember, is quick to type, and is pretty difficult to ever phat phinger. :smiley: