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

Question

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

i have a scenario in which users does not enter any date Does it works with another language than MySQL ? — Anwar
– Anwar, Commented Jun 29, 2015 at 7:52

Lalit Kumar B · Accepted Answer · 2015-06-29 09:00:24Z

2

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.

edited Jun 29, 2015 at 9:00

answered Jun 29, 2015 at 8:41

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evaldas Buinauskas · Accepted Answer · 2015-06-29 07:52:26Z

1

There are couple of options:

Using Dynamic SQL to build your queries based on user input
Using multiple IF/ELSE statements to build your query based on user input
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.

answered Jun 29, 2015 at 7:52

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

6 Comments

dev90 Over a year ago

Thank you, I would go with 3rd Option :)

Evaldas Buinauskas Over a year ago

It's probably easiest and most readable. However, I personally think 2nd one would perform better (from my own experience), especially if your data grows fast. Let me know if this works for you. :)

dev90 Over a year ago

sure,I will let you know.. Thanks :)

Lalit Kumar B Over a year ago

No need to use PL/SQL, do it in SQL using NVL.

Evaldas Buinauskas Over a year ago

@LalitKumarB That's right. You can use NVL to replace NULL values or define default parameters in procedure description, such as PROCEDURE P1 (Param1 IN NUMBER := 100); So it's either users' input or default value. Up to personal taste or/and SQL standarts in your own company I guess.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related