CODEWITHSUNDEEP
sqloracle-databasepredicatebetween
dev90
dev90

Reputation: 7529

How to Use Between Clause in Sql query without using and clause

I have created a query which gets results between 2 dates, Its working fine.. Now i have a scenario in which users does not enter any date..In this case I need to fetch all the result from database regardless of date.. The following query fails when user does not enter any date, it returns empty result set.
Kindly guide me how to resolve this problem.

select * from emp_register
where date between ' 2015-03-26 15:42:52' and ' 2015-03-26 15:42:52' 
or status= 'Y'

Date Format is Timestamp

Upvotes: 2

Views: 1076

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

where date between ' 2015-03-26 15:42:52' and ' 2015-03-26 15:42:52'

' 2015-03-26 15:42:52' is NOT a DATE, it is a string. Never ever rely on implicit data type conversion. You might just be lucky to get the result depending on your NLS settings, however, it will certainly fail when the client NLS settings will be different for others.

Now i have a scenario in which users does not enter any date..In this case I need to fetch all the result from database regardless of date

No need to do in PL/SQL, do it in SQL.

Use NVL function on the values. Use a default lower bound and upper bound date values in the BETWEEN clause for the NVL.

For example,

SELECT *
FROM emp_register
WHERE DATE BETWEEN NVL(:date1, DATE '1900-01-01') AND NVL(:date2, DATE '9999-01-01')
OR status= 'Y'

So, whenever :date1 and/or :date2 and/is NULL, then it would use the default date values to fetch all the rows from the table.

Upvotes: 2

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

There are couple of options:

  1. Using Dynamic SQL to build your queries based on user input
  2. Using multiple IF/ELSE statements to build your query based on user input
  3. Setting default values for start and end dates that would always fit in your time range, such as 1900-01-01 and 2300-01-01 if user didn't pass any.

Upvotes: 1

Related Questions