When implementing datetime time pairs, what is the best approach?
Approach 1
_RK FROM_DTTM TO_DTTM
1 01-JAN-2012 00:00:00 31-DEC-2012 23:59:59 ------------> Old record closed out
1 01-JAN-2013 00:00:00 31-DEC-4949 23:59:59 ------------> New active record
Used with a filter clause as follows:
where FROM_DTTM <= &FILTER_DATE <= TO_DTTM
Approach 2
_RK FROM_DTTM TO_DTTM
1 01-JAN-2012 00:00:00 01-JAN-2013 00:00:00 ------------> Old record closed out
1 01-JAN-2013 00:00:00 31-DEC-4949 23:59:59 ------------> New active record
Used with a filter clause as follows:
where FROM_DTTM <= &FILTER_DATE < TO_DTTM
Questions:
Approach 1 is compatible with the syntax of the BETWEEN clause (inclusive). For this reason, I think this is the best approach, however - and this is my concern - there will always be a gap between the closing and opening marks! For example, the following (wrong from a business point of view) returns nothing:
where FROM_DTTM <= '31-DEC-2012 23:59:59.1'dt <= TO_DTTM
. , ( ), , - 1 (, - AS OF)? , ( ) 2 .