Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Unanswered: Output n a certain format

    Hi,

    I have MON-YYYY, and I want the output to be 12*YYYY + (number of the Month -1). For example,

    APR-2004 -> 12*2004 + (4-1) = 24048 + 3 = 24051. I want the output to be in this format.

    Can anybody show me how to do this?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Output n a certain format

    select 12 * TO_NUMBER(TO_CHAR(dateval,'YYYY')) + TO_NUMBER(TO_CHAR(dateval,'MM')) - 1
    from table;

  3. #3
    Join Date
    Apr 2002
    Posts
    56
    You seem to need the number of months from the year 0
    to (and not including) the selected month

    This wil, do the trick

    select months_between(to_date('APR-2004','MON-YYYY'),to_date('000101','YYYYMM')) + 12 from dual

    Prolem here is that the year 0 does not exist, so I took year 1 and added 12 months for year 0.

    Alternative:

    select
    12 * substr(to_char(to_date('APR-2004','MON-YYYY'),'YYYYMM'),1,4)
    +
    substr(to_char(to_date('APR-2004','MON-YYYY'),'YYYYMM'),5,2) - 1
    from dual

  4. #4
    Join Date
    Feb 2004
    Posts
    35
    Thank You guys,

    I need the way Andrew suggested. Thanks Edwin. 1 problem, different solutions. That's great to know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •