Help with SQL query using dates

Question

I am a little rusty on my SQL and am having a little trouble with my SQL query. Here is my current query:

 SELECT DISTINCT restriction FROM restrictions 
 WHERE id = ? AND start_date 
 BETWEEN TO_DATE(?, 'yyyy/mm/dd') and TO_DATE(?, 'yyyy/mm/dd') ;

The question marks are being set depending on a user defined parameter, but that should not matter here. So here I am getting any restriction between two particular date, and this works fine. I need to add another condition to this though. Something like:

OR start_date < TO_DATE(?, 'yyyy/mm/dd') and end_date is NULL;

I know what I have here is incorrect, but it should give you an idea.....I want all of the above conditions (the one in the first code snippet), and I want it to also to grab any restrictions that have a start date previous to the first '?', and have a corresponding end_date that is null (second code snippet). I believe this can be done in one SQL query, any help is appreciated, and if you have questions, please ask.

D'Arcy Rittich · Accepted Answer · 2011-06-01 14:10:30Z

1

SELECT DISTINCT restriction 
FROM restrictions   
WHERE id = ? 
    AND (start_date BETWEEN TO_DATE(?, 'yyyy/mm/dd') and TO_DATE(?, 'yyyy/mm/dd')
        OR start_date < TO_DATE(?, 'yyyy/mm/dd') and end_date is NULL);

answered Jun 1, 2011 at 14:10

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

1 Comment

Mr Man Over a year ago

I guess I was closer than I thought....This does work, thank you for the quick help/response!

Blazes · Accepted Answer · 2011-06-01 14:11:18Z

0

I think this is what you are asking for:

SELECT DISTINCT restriction FROM restrictions 
WHERE id = ? AND 
(start_date BETWEEN TO_DATE(?, 'yyyy/mm/dd') and TO_DATE(?, 'yyyy/mm/dd'))
OR (start_date < TO_DATE(?, 'yyyy/mm/dd') and end_date is NULL)

answered Jun 1, 2011 at 14:11

Blazes

4,7792 gold badges25 silver badges29 bronze badges

Collectives™ on Stack Overflow

Help with SQL query using dates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related