DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Extract Fiscal Week

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455

    Extract Fiscal Week

    I have a table t1 with col1 with values DAYMONYear

    col1
    13012003
    ....
    ....
    27032003
    28032003
    29032003

    Question is how do I find out the fiscal week of the each row (Fiscal week 1 = Jan1-Jan7)

    Thanks in Advance

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    I got it
    select col1, to_char(to_date(col1, 'DD/MM/YYYY'), 'WW') from t1

    Thanks

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Best to store these dates in Data format.

    Anyway, convert the numbers to dates ...

    to_date(to_char(col1),'DDMMYYYY')


    ... then extract the day of the year ...

    to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD')

    ... convert it to a number ...

    to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))

    ...

    then divide by seven, ignore remainder, and add 1...


    floor(to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))/7)+1

    ... and that should be close.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    ... and that should be close.
    Hm, TO_CHAR(v_date, 'WW') should be even closer. And it's allready built in, and it's a hell lot shorter.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    Best to store these dates in Data format.

    Anyway, convert the numbers to dates ...

    to_date(to_char(col1),'DDMMYYYY')


    ... then extract the day of the year ...

    to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD')

    ... convert it to a number ...

    to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))

    ...

    then divide by seven, ignore remainder, and add 1...


    floor(to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))/7)+1

    ... and that should be close.
    I'm having flashbacks from Freshman Calculus...
    Jeff Hunter

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think I'm having flashbacks to trying to calculate a client's nonsensical fiscal weeks. of course WW is easier.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width