Fiscal Week

[irehman]

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

[slimdave]

how does your organization define a fiscal week

[irehman]

Jan 1st through 7th is First week, and so on.

[slimdave]

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

[DaPi]

It works better if you put a /7 in the code:

select trunc(to_number(to_char(sysdate,'DDD'))/7)+1 from dual;

[slimdave]

well, if you want to be picky, i suppose you do need it.

[mshyampc]

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.

[slimdave]

Then I would replace sysdate in the above with add_months(sysdate,-3)

[Bore]

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

[slimdave]

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