This discussion is archived
9 Replies Latest reply: Jun 14, 2010 3:42 AM by Solomon Yakobson RSS

Getting the month's number

Ken_73 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Hi,

    How about
    select EXTRACT(MONTH FROM sysdate) from dual;
    Regards
    Peter
  • 2. Re: Getting the month's number
    Ken_73 Newbie
    Currently Being Moderated
    Cool. Thank you very much Peter
  • 3. Re: Getting the month's number
    Prathamesh Explorer
    Currently Being Moderated
    select to_number(to_char(sysdate, 'mm')) from dual
  • 4. Re: Getting the month's number
    Nimish Garg Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    check this query you said is wrong......

    SELECT TO_NUMBER(ADD_MONTHS(sysdate),3),'MM')) from dual
  • 8. Re: Getting the month's number
    626204 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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