This is a question I’ll try to answer here. Quite frequently it happens to face SQL code, where extremum dates take place. From a first glance it look OK to use like to_date(‘3000.01.01′,’YYYY.MM.DD’)) for indicating infinity, something that lasts forever. Nobody could bet that no one of our developed nice IT system will even exist at such time point 🙂
When do we use it ?
In relational database, when need to indicate time span when record is valid, we use simple valid_from and valid_to dates. And for active records which have validity till undefined date, means forever, we simply leave null for valid_to. Because this is what it means – null – not known. We really don’t know so far when this record will stop exist as valid. And thats logically OK. But if someone put valid_to = to_date(‘4000.01.01′,’YYYY.MM.DD’) treating it as infinity, which will never be reached. Logically looks OK as well, we really never will reach it. But when trying to write SQL statements which deal with date spans defined in such way, and the same extremely big date “trick” use in SQL, we can face some situations which results in wrong data response.
Lets consider a sample
Assume, we have HR table employee_contracts, where we keep dates for time span when particular employee contract is valid:
create table employee_contracts ( id number, code nvarchar2(100), valid_from date, valid_to date);
Also assume, that we allow end-users in our HR application to enter those dates in a form. User enters for some contracts valid_to date 4000.01.01, treating it as it is unlimited.
Then you’re asked to write an SQL which delivers report about contracts in time span, provided by user as a parameter. You’re also expecting that user on her choice can not provide any of date interval parameters at all. In this case you have to return all contracts. An here is SQL with extremum dates:
select
id, code
from employee_contracts
where
valid_from >= nvl(:param_valid_from,to_date('1000.01.01','YYYY.MM.DD'))
and valid_to <=nvl(:param_valid_to,to_date('3000.01.01','YYYY.MM.DD'))
Here you have own position what is infinity, in your case its 3000.01.01. Apparently, if user does not provide valid_to date, expecting that all unlimited contracts will be returned, he’ll not get any records.
Probably you think – come on, its so straight forward, that it is unreasonable to use extremum dates, why do even have talk about it! True, but we may have a temptation to get in this trap, because there really are cases when it is safe. But in general it is risky usage, bad practice. Never know how data will emerge and cause bugs…
Preceding sample, avoiding extremum dates, could be written simply this way:
select id, code from employee_contracts where valid_from >= nvl(:param_valid_from,valid_from) and valid_to <=nvl(:param_valid_to,valid_to))
Yeah.. Thats simple, straight forward, but still worth to mention 🙂