devxlogo

Use ‘open’ Date in the Oracle SQL Where Clause

Use ‘open’ Date in the Oracle SQL Where Clause

The most common example where ‘open’ date is used is in the expiration_date for an entry. It could be set for a particular date, or be NULL if the expiration date isn’t defined (open date). It is also possible to ‘open’ date use in the where clause:

 EXP_DATE is NULL or(EXP_DATE is NOT NULL and EXP_DATE 

Assume that argument :ar_date could be NULL as well. Oracle doesn't allow you to use 'is Null' for arguments, but function nvl() could be used instead:

 EXP_DATE is NULL orNvl(:ar_date,' ') = ' ' or(EXP_DATE is NOT NULL and   Nvl(:ar_date,' ')  ' ' and   EXP_DATE 

All the above could be combined in the single comparison expression:

 nvl(to_char(trunc(:ar_date),'YYYYMMDD'),'00000000')) 

First, the date's comparison is substituted by the comparison of strings. If there are any null values, in both cases they will be substituted by the minimum or maximum numbers--depending what side of comparison they are located. This is how the open date is treated. Second, the comparison operator could be '
Usage example: If there is a need to select entries that were active in a particular date range (from :ar_from to :ar_to) and all table's values and arguments could be null, SELECT will be:

 SELECT …FROM …WHERE… andnvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) 

devx-admin

Share the Post: