9 Replies Latest reply: Jun 14, 2010 5:42 AM by Solomon Yakobson RSS

    Getting the month's number

    Ken_73
      DB version:10gR2

      I have been asked to tune a slow performing query.

      I think it is trying to get the month's number(like 6 for june, 7 for july) after some month adding logic. I have replaced the date column with sysdate.

      The query looks like
      SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('01-'||TO_CHAR(sysdate,'MON-RRRR'),'DD-MON-RRRR'),3),'MM')) from dual
      Is there any room for improvement?
        • 1. Re: Getting the month's number
          Peter Gjelstrup
          Hi,

          How about
          select EXTRACT(MONTH FROM sysdate) from dual;
          Regards
          Peter
          • 2. Re: Getting the month's number
            Ken_73
            Cool. Thank you very much Peter
            • 3. Re: Getting the month's number
              Prathamesh
              select to_number(to_char(sysdate, 'mm')) from dual
              • 4. Re: Getting the month's number
                Nimish Garg
                try
                select EXTRACT(MONTH FROM sysdate) from dual;
                or
                select to_char(sysdate,'mm') from dual;
                • 5. Re: Getting the month's number
                  Solomon Yakobson
                  Ken_73 wrote:

                  The query looks like
                  SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('01-'||TO_CHAR(sysdate,'MON-RRRR'),'DD-MON-RRRR'),3),'MM')) from dual
                  Is there any room for improvement?
                  Ton. First of all:
                  TO_DATE('01-'||TO_CHAR(sysdate,'MON-RRRR'),'DD-MON-RRRR')
                  Yes, it will give you first of the current month, but at a cost of two conversions and concatenation. To get first of the month use TRUNC function:
                  TRUNC(sysdate,'mm')
                  But irony is, you do not need first of the month. When you add/subtract N months from a date it does not matter which day of the month date is - result month will be same for any day of the month. So:
                  SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,3),'MM')) from dual
                  /
                  or
                  SELECT EXTRACT(month from ADD_MONTHS(sysdate,3)) from dual
                  /
                  will give you what you need.

                  SY.

                  Edited by: Solomon Yakobson on Jun 14, 2010 3:28 AM
                  • 6. Re: Getting the month's number
                    Nimish Garg
                    how this query is slow???

                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 1388734953

                    -----------------------------------------------------------------
                    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
                    -----------------------------------------------------------------
                    | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
                    | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
                    -----------------------------------------------------------------

                    Statistics
                    ----------------------------------------------------------
                    0 recursive calls
                    0 db block gets
                    0 consistent gets
                    0 physical reads
                    0 redo size
                    603 bytes sent via SQL*Net to client
                    488 bytes received via SQL*Net from client
                    2 SQL*Net roundtrips to/from client
                    0 sorts (memory)
                    0 sorts (disk)
                    1 rows processed
                    • 7. Re: Getting the month's number
                      743848
                      check this query you said is wrong......

                      SELECT TO_NUMBER(ADD_MONTHS(sysdate),3),'MM')) from dual
                      • 8. Re: Getting the month's number
                        Prazy
                        orza wrote:
                        check this query you said is wrong......
                        It is not, you just have to remove the close parentheses that comes after sysdate and add a to_char before add_months.

                        Why don't you just do some work around before naming it "Wrong" :)
                        SELECT TO_NUMBER(to_char(ADD_MONTHS(sysdate,3),'MM')) from dual
                        Regards,
                        Prazy
                        • 9. Re: Getting the month's number
                          Solomon Yakobson
                          orza wrote:
                          check this query you said is wrong......
                          Thanks, cut&paste error - missing TO_CHAR. Should be:
                          SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(sysdate,3),'MM')) from dual
                          /
                          SY.