Oracle to_date function with quarter-format

Question

I need to find some records created in a range of quarters. For example, I'm looking for all records created between the 4th quarter of 2008 and the 1st quarter of 2010. I have this in my WHERE-clause:

...and r.record_create_date between to_date('2008 4','YYYY Q')
                                and to_date('2010 1','YYYY Q')

but Oracle says: ORA-01820: format code cannot appear in date input format. The Q is a valid date format symbol, so I'm not sure what's happened. Is this even a valid way to find values in between calender quarters, or is there a better way?

---

Also interesting, and possibly related, if I execute this:

select to_date('2009','YYYY') from dual;

The value displayed in my IDE is 2009-08-01. I would have expected 2009-08-04, since today is 2010-08-04.

This:

select to_date('2009 1','YYYY Q') from dual;

of course, fails.

(Oracle 10g)

Answer 1

Oracle says: ORA-01820: format code cannot appear in date input format. The Q is a valid date format symbol, so I'm not sure what's happened.

See the second column of table 2.15 at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34948. Not all format elements are allowed when converting to dates, timestamps, etc.

I recommend against using between for date range checks. People often will miss values within the ending day that the expect to be included. So I would translate:

and r.record_create_date between to_date('2008 4','YYYY Q')
                             and to_date('2010 1','YYYY Q')

To

and to_date('2008-10-01', 'YYYY-MM-DD') <= r.record_create_date 
and record_create_date < to_date('2010-04-01', 'YYYY-MM-DD') -- < beginning of 2Q2010.