Using 9999-01-01 is no more of kludge than using NULL but the former has advantages over using NULL when it comes to performance, especially if you understand that the use of things like COALESCE on a temporal column will produce a Non-SARGable query. If you follow the true definition of NULL, NULL also does not mean that there IS an stop date. The definition of NULL is UNKNOWN, which also means there may or may not be one but we just don't know. NULL, therefore, is a kludge where someone assigns the irrational value of INFINITY to it rather than using its true meaning of UNKNOWN. Talk about kludges.
Since there is no actual functionality in SQL Server (or any other RDBMS that I know of) that has a built in "value" for INFINITY, even Mr. Snodgrass used '9999-12-31' in chapter 2 of his PDF (located at the following URL which was on the reference page you cited, thank you) as his "Forever" date. He doesn't even mention the use of nulls for stop dates until chapter 3 and then uses them only with the temporal criteria operator of "OVERLAPS", which is not available in SQL Server and so rendering it a moot point for the discussion on this thread.
Mr. Snodgrass also acknowledges the limitations of RDBMS software as not having a valid constant for "Infinity" and that's why even he uses a non-null surrogate of 9999-12-31 to represent a value in the extreme future.
Now, I DO absolutely agree that using a non-null, non-infinity surrogate for "in perpetuity" does have a limit where it should not and that it cannot be meant to mean "in perpetuity". I have not yet come across a use case that is active in SQL Server that would require such a value as "in perpetuity". If I did, then I would likely use NULL, even though that would certainly be a kludge. But for most things, an extreme future date occurring sometime in the last year (9999) that SQL Server is capable of recognizing and storing will suffice.
Using such a surrogate values as '9999-01-01' also has performance and resource usage advantages over using NULL for the same thing AND it DOES actually follow the ISO standard of "Half Open" assignment of periods. The examples in chapter 2 that Mr. Snodgrass created use the "Half Open" period dates, as do I. That's also why I use '9999-01-01' so that there's headroom to add a day, week, or month to a stop date at the query level without violating the maximum date of 9999-12-31 that SQL Server can recognize. It supports the idea/standard of always using "Half Open" dates.
It actually surprises me a whole lot for you to even mention "Half Open" dates to define periods because of the total fits you've thrown on other threads about how BETWEEN should be used for such a thing and my adamant stance on those same threads (which you incorrectly chastised me for) that you should never use BETWEEN for such a things. Please make up your mind as to which stance you're going to take because if you actually do support the concept of "Half Open" period notation and reject BETWEEN as a viable method for doing so, you and I don't have an argument.
I'll also state that the 24:00:00 issue is well known (there is no such time, actually) but that's also a moot point because I've not suggested it's use and so I don't know why you even though it worth mentioning. But, since you brought it up, you are incorrect because you can't actually use 24:00:00 as a time in SQL Server as the following code proves.
SELECT CONVERT(DATETIME,'2020-01-01 24:00:00'); --Creates an out-of-range error
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)