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 < :ar_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 < :ar_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 '<' or '<=', which doesn't limit the technique. Plus, this method makes SQL more readable as can be seen in the example below (rewrite the example using another technique if you are not sure about this).

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')) <=nvl(to_char(trunc(:ar_from),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(:ar_from),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(:ar_to),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(:ar_to),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999');
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS