Fiscal Week
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Fiscal Week

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

    Fiscal Week

    I have a table with row_timestamp column, is there a build in function in oracle to extract data by fiscal week, out of row_timestamp?

    Thanks in Advance

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    how does your organization define a fiscal week
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Jan 1st through 7th is First week, and so on.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    in that case you just need to divide the day of the year by seven an and one, right?

    week_no= trunc(to_number(to_char(time_stamp,'DDD')))+1
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It works better if you put a /7 in the code:

    select trunc(to_number(to_char(sysdate,'DDD'))/7)+1 from dual;
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    well, if you want to be picky, i suppose you do need it.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Apr 2007
    Posts
    1

    Fiscal Year starting on 1st May

    Hi,

    I went through the thread and this is really useful, I want to extract the Fiscal Week from the sysdate and my Fiscal Year starts on 1st April.

    Please advise.

    Thanks.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Then I would replace sysdate in the above with add_months(sysdate,-3)
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Guys,
    Does anyone consider reading the documentation before asking?
    The SQL Refference says
    Date format models
    IW Week of year (1-52 or 1-53) based on the ISO standard.
    so
    select to_number(to_char(sysdate,'IW')) from dual;
    Is supposed to return the number of the CALENDAR week of the current date.
    In the first case, when fiscal year starts at 1st of january, that seems to be the same
    In the second case I would go for

    select to_number(to_char(sysdate,'IW')) - to_number(to_char(to_date(1st of april),'IW')) from dual;

    So really, the Oracle documentation is great thing
    I would recommend
    SQL Reference
    Database Administration guide
    Concepts guide

    Can be found at
    www.oracle.com/technology

    :-)
    Cheers
    Boris

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You're reading it wrongly. IW weeks always start on a Monday. WW would do the job though


    http://download-east.oracle.com/docs...l.htm#CIHGFJEI
    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