CODEWITHSUNDEEP
sqldatabaseoracle-database
Komaroju 1991
Komaroju 1991

Reputation: 1

How to pass a date in query

I have a table which has a begin date and an end date. I have to fetch these dates and check whether these dates are in the range of a column value of another table...

I have tried by fetching begindate, and enddate using a select query and have used the between keyword to check the range 

select invoice 
from customer 
where BOOK_DATE BETWEEN(select begindate, enddate 
                        from Period 
                        where i_status = 1)

Actual result : missing key word Expected result : Ex: Begin Date = 1-May-19, End Date = 31-May-19 and book_date = 15-May-19, it should return invoice num something like '123ABC'

Upvotes: 0

Views: 176

Answers (2)

sticky bit
sticky bit

Reputation: 37487

You can use EXISTS and a correlated subquery that checks if a row in period exists, where the booking date is between the begin and end date and the status is 1.

SELECT c.invoice
       FROM customer c
       WHERE EXISTS (SELECT *
                            FROM period p
                            WHERE c.book_date BETWEEN p.begindate
                                                      AND p.enddate
                                  AND p.i_status = 1);

Upvotes: 2

likwidfire2k
likwidfire2k

Reputation: 195

Between has an AND between them, you should try 

BETWEEN(SELECT begindate FROM Period WHERE i_status = 1) AND (SELECT enddate FROM 
Period WHERE i_status = 1)

Upvotes: 0

Related Questions