CODEWITHSUNDEEP
sqloracle-database
user114671
user114671

Reputation: 532

Oracle SQL to include null dates in where clause

I have the following data:

id | actual_start
 1 | (null)
 2 | 03/03/2014
 3 | 27/03/2014

I want to be able to use a use input which will select all dates including and after the date input, but the nulls are causing me a problem. I only want the nulls included if the user leaves the input blank.

This is what I have:

where actual_start >= nvl(to_date('&start_date','dd/mm/yyyy'), to_date('01/01/2000', 'dd/mm/yyyy'))

This is fine for when the user does actually enter a date. However, if the user leaves it blank, then I get this:

id | actual_start
 2 | 03/03/2014
 3 | 27/03/2014

which is no good.

The other idea I had was to try:

where (actual_start >= nvl(to_date('&start_date','dd/mm/yyyy'), to_date('01/01/2000', 'dd/mm/yyyy'))
or actual_start is null)

but this always includes the nulls - i.e. it works for the user inputting nothing, but if the user inputs 20th March, it returns:

id | actual_start
 1 | (null)
 3 | 27/03/2014

which isn't okay.

Many thanks for your help.

Upvotes: 0

Views: 4131

Answers (1)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

Try this one:

where nvl(actual_start, to_date('01/01/2000', 'dd/mm/yyyy')) >=
 nvl(to_date('&start_date','dd/mm/yyyy'), to_date('01/01/2000', 'dd/mm/yyyy'))

Although it has side-effect that index for actual_date column cannot be used, therefore may potentially lead to performance problems on large datasets.

Update: The other one you can use is your second condition slightly modified:

where actual_start >= 
 nvl(to_date('&start_date','dd/mm/yyyy'), to_date('01/01/2000', 'dd/mm/yyyy'))
or nvl('&start_date',actual_start) is null

Upvotes: 1

Related Questions