0

I had the follow instruction on Oracle SQL:

SELECT TO_DATE(FE_DAY || '/' || FE_MO || '/' || FE_YEAR, 'dd/mm/yyyy')
 FROM TI_DATE
WHERE FE_DAY =15 BETWEEN TO_DATE('01/01/2011') AND TO_DATE('31/12/2012')

and effectively I get all the dates that contains the day 15 between that intervals

But I need other thing to do.

How can I show all dates that contain

  • FE_Day=15

  • FE_MO=02

  • FE_MO=05

  • FE_MO=07

  • BETWEEN FE_YEAR= 2011 and FE_YEAR=2012

    from the TI_DATE table?

Thanks by your help.

Community
  • 1
  • 1
Christian Magro
  • 139
  • 1
  • 1
  • 11
  • Side note: don't omit `TO_DATE()`'s second argument. Default date format is not universal. – Álvaro González Nov 21 '12 at 15:45
  • Just so you know your where clause in your original statement is wrong. Predicates in a where clause need to be separated by either `AND` or `OR`. Also the second predicate `BETWEEN` is missing somthing to the left. What is it that you want to be between 01/01 and 31/12 – Conrad Frix Nov 21 '12 at 15:51
  • ... If this is a date-dimension type table (given the name and usage), does it have an actual 'date' column, instead of you needing to construct the date at the end? Also, please avoid using `BETWEEN` for any range, especially for dates - use an exclusive upper-bound (`'<'`) whenever possible. – Clockwork-Muse Nov 21 '12 at 16:51

1 Answers1

1

I think you should dispense with thinking of these as dates. Just implement your rules in a WHERE clause:

where (FE_Day = '15' or FE_MO in ('02', '05', '07')) and
      FE_YEAR in ('2011', '2012')

I've enclosed everything in single quotes, because I'm guessing the data types are characters.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wouldn't that be `FE_Day = '15' AND FE_MO IN` Which of course would mean the outer parens wouldn't be needed – Conrad Frix Nov 21 '12 at 15:53
  • @ConradFrix . . . When I read the question, I wasn't sure if the logic was "and" or "or". However, since the months took on multiple values, they were clearly connected by "or"s. So, I assumed everything was connected by "or"s. Even if I got that wrong, the idea should still be helpful. – Gordon Linoff Nov 21 '12 at 15:55
  • I agree the idea is helpful (which is why I upvoted it) and I guess my assumption would be that they want the records for 15 for certain months. Wanting the records for Feb, May, and July and only the 15 of the other months seems weird. – Conrad Frix Nov 21 '12 at 16:28