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
how does your organization define a fiscal week
Jan 1st through 7th is First week, and so on.
in that case you just need to divide the day of the year by seven an and one, right?
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
well, if you want to be picky, i suppose you do need it.
Fiscal Year starting on 1st May
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.
Then I would replace sysdate in the above with add_months(sysdate,-3)
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.
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
Database Administration guide
Can be found at
You're reading it wrongly. IW weeks always start on a Monday. WW would do the job though
Click Here to Expand Forum to Full Width