CODEWITHSUNDEEP
sqldb2
Kashish Aneja
Kashish Aneja

Reputation: 41

Get all rows with a list of date between two date columns in SQL

I have a table named tableA which has two date columns. Currently, I am using the below query to fetch data.

"select * from tableA where IN_Date between date1 and date2"

IN_DATE is input param from the proc

Now instead of one date IN_DATE, I want to pass a list of dates but I am not sure how to update the query. Please help.

TableA

id date1 date2

Upvotes: 0

Views: 338

Answers (3)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You may use a string tokenization approach like below, where the IN_DATE string parameter has comma separated list of dates in the form of YYYY-MM-DD.

select *
from tableA t
where exists
(
  select 1
  from xmltable 
  (
    'for $id in tokenize($s, ",") return <i>{normalize-space ($id)}</i>' 
    passing IN_DATE as "s"
    columns 
      tok char(10) path '.'
  ) v
  where date (to_date (v.tok, 'YYYY-MM-DD')) between t.date1 and t.date2
)

Upvotes: 0

maxweld
maxweld

Reputation: 239

What you are trying to do simply is not possible.

The syntax of the between clause is:

... expression1 BETWEEN expression2 AND expression3 ...

Each expression must resolve to a single value (not a list of values). Furthermore expression2 be < expression3, otherwise results are undefined. Where expressionN is a column name, then the single value is the value in the row currently being evaluated.

This suggests that you may be approaching this incorrectly. Please provide some sample data, and expected results. This will allow a better understanding of what you are trying to do. A description of what you are wanting to achieve would also be helpful, rather than a description of how you are trying to achieve it.

Upvotes: 0

Ramesh Yele
Ramesh Yele

Reputation: 11

The solution to your problem

select * from tableA where (
    (IN_Date between date1 and date2) or 
    (IN_Date between date3 and date4) or 
    (IN_Date between date5 and date6)
)

Upvotes: 0

Related Questions