10 Replies Latest reply: Jun 3, 2010 10:38 AM by InoL RSS

    to_date and quarter format

    InoL
      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
          '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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Even easier indeed!