1

I can get the 15/02 - 21/06 date range data on every year from the query below.

SELECT * FROM dim_date
WHERE EXTRACT (MONTH FROM date_cal) BETWEEN 3 AND 5
 OR (EXTRACT (MONTH FROM date_cal) = 2 AND EXTRACT (DAY FROM date_cal) >= 15)
 OR (EXTRACT (MONTH FROM date_cal) = 6 AND EXTRACT (DAY FROM date_cal) <= 21)

But the problem is, when i want to find from year 2010 - 2012, I get back the same result.

Range that i want to get:

2010-02-15 - 2010-06-15
2011-02-15 - 2011-06-15
2012-02-15 - 2012-06-15

SELECT * FROM dim_date
WHERE EXTRACT (MONTH FROM date_cal) BETWEEN 3 AND 5
 OR (EXTRACT (MONTH FROM date_cal) = 2 AND EXTRACT (DAY FROM date_cal) >= 15)
 OR (EXTRACT (MONTH FROM date_cal) = 6 AND EXTRACT (DAY FROM date_cal) <= 21)
AND EXTRACT (YEAR FROM date_cal) BETWEEN 2010 AND 2012

Who can help me about this!

Bohemian
  • 412,405
  • 93
  • 575
  • 722
tommy5115
  • 105
  • 2
  • 10
  • Consider the [follow-up question with more answers](http://stackoverflow.com/q/11944762/939860). And [this related question with much more on the topic](http://stackoverflow.com/questions/15169410/how-do-you-do-date-math-that-ignores-the-year/15179731). – Erwin Brandstetter May 02 '13 at 23:32

1 Answers1

3

Your problem is a lack of brackets: In SQL AND takes precedence over OR, so you need to put brackets around your whole previous expression:

SELECT * FROM dim_date
WHERE ( -- added bracket
 EXTRACT (MONTH FROM date_cal) BETWEEN 3 AND 5
 OR (EXTRACT (MONTH FROM date_cal) = 2 AND EXTRACT (DAY FROM date_cal) >= 15)
 OR (EXTRACT (MONTH FROM date_cal) = 6 AND EXTRACT (DAY FROM date_cal) <= 21)
 ) -- added bracket
AND EXTRACT (YEAR FROM date_cal) BETWEEN 2010 AND 2012

Without the brackets, you get A or B or (C and D), but you want (A or B or C) and D

Bohemian
  • 412,405
  • 93
  • 575
  • 722