This discussion is archived
10 Replies Latest reply: Jun 3, 2010 8:38 AM by InoL RSS

to_date and quarter format

InoL Guru
Currently Being Moderated
To_date and to_char formats usually work both ways. But not for quarter (Q):
select to_char(sysdate,'q') from dual;

select to_date('2','q') from dual;

T
-
2


1 row selected.

select to_date('2','q') from dual
                   *
Error at line 1
ORA-01820: format code cannot appear in date input format
Any reason why to_date won't accept Q as format? I would expect it to return the first day of the quarter of the current year, just like using MM returns the first day of the month of the current year:
select to_date('2','mm') from dual;

TO_DATE('
---------
01-FEB-10
I tried it on 9.2.0.8 and 10.2.0.1 (XE).
  • 1. Re: to_date and quarter format
    bluefrog Expert
    Currently Being Moderated
    'q' is not accepted as a valid literal for the format mask to the to_date function, but is vakid for the to_char function!
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00210

    >
    Q No Quarter of year (1, 2, 3, 4; January - March = 1).
    >

    whereas this is acceptable;

    >
    MON Yes Abbreviated name of month.
  • 2. Re: to_date and quarter format
    Ludock Explorer
    Currently Being Moderated
    Dont know why the Q mask is not working
    select to_date(2,'Q') from dual
    but you can do it with MM
    select to_date(2*3-2,'MM') from dual
    L.
  • 3. Re: to_date and quarter format
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Sorry, I don't know why that's not allowed.
    The format elements for weeks ('IW', 'W' and 'WW') can't be used in TO_DATE either.
    This behavior may be inconvenient, but at least it's well-documented: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements004.htm#sthref402

    You can write your own function, or use a CASE expression to map the quarters to months:
    TO_DATE ( CASE  q
                WHEN  '1'     THEN  '01'
                WHEN  '2'     THEN  '04'
                WHEN  '3'     THEN  '07'
                WHEN  '4'     THEN  '10'
           END
         , 'MM'
         )
    Edited by: Frank Kulash on Jun 3, 2010 11:02 AM
  • 4. Re: to_date and quarter format
    InoL Guru
    Currently Being Moderated
    Oops, I completely overlooked the column "Specify in TO_* datetime functions?" Indeed it says No for Q format.

    The CASE statement is easy, but I was looking for the easiest way :-)
  • 5. Re: to_date and quarter format
    Boneist Guru
    Currently Being Moderated
    An easier way:
    select add_months(trunc(sysdate, 'yyyy'), (:quarter_no - 1)*3)
    from   dual;
    Edited by: Boneist on 03-Jun-2010 16:32
    Changed from addition to add_months (d'oh!)
  • 6. Re: to_date and quarter format
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    InoL wrote:
    ... The CASE statement is easy, but I was looking for the easiest way :-)
    DECODE is a little shorter than CASE.
    TO_DATE ( DECODE ( q, '1', '01'
                    , '2', '04'
                    , '3', '07'
                    , '4', '10'
               )
         , 'MM'
         )
    If you're certain that q is '1', '2', '3' or '4' (or NULL), then you can use Ludock's suggestion:
    TO_DATE ( (q * 3) - 2
         , 'MM'
         )
    But if q is any other value, you'll get specious results, if not a run-time error.
    Boneist wrote:
    An easier way:
    select trunc(sysdate, 'yyyy') + (:quarter_no - 1) * 3
    from   dual
    Won't that always return January 10 or earlier?
    Maybe you meant to use ADD_MONTHS instead of addition.
  • 7. Re: to_date and quarter format
    InoL Guru
    Currently Being Moderated
    Thanks all, but I already had the workaround:
    select to_date(:q*3-2,'mm') from dual;
    Or today's quarter:
    select to_date(to_number(to_char(sysdate,'q'))*3-2,'mm') from dual;
  • 8. Re: to_date and quarter format
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    InoL wrote:
    ... Or today's quarter:
    select to_date(to_number(to_char(sysdate,'q'))*3-2,'mm') from dual;
    Even easier:
    SELECT  TRUNC (SYSDATE, 'Q')
    FROM    dual;
  • 9. Re: to_date and quarter format
    Boneist Guru
    Currently Being Moderated
    Frank Kulash wrote:
    Boneist wrote:
    An easier way:
    select trunc(sysdate, 'yyyy') + (:quarter_no - 1) * 3
    from   dual
    Won't that always return January 10 or earlier?
    Maybe you meant to use ADD_MONTHS instead of addition.
    hahaha, total brainfail. You're right, I did mean to use add_months; I must have misread the dates when I tested it out {noformat}*slaps forehead*
    toddles off to correct
    {noformat}
  • 10. Re: to_date and quarter format
    InoL Guru
    Currently Being Moderated
    Even easier indeed!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points