Help with SQL query using dates
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开发者_StackOverflow社区 believe this can be done in one SQL query, any help is appreciated, and if you have questions, please ask.
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);
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)
精彩评论