OraFAQ Forum: SQL & PL/SQL » Display Week number as per financial year(Apr

Home » SQL & PL/SQL » SQL & PL/SQL » Display Week number as per financial year(Apr - Mar) (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Display Week number as per financial year(Apr - Mar) [message #572268]

Sun, 09 December 2012 12:01

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Dear Friends,

I hava an requirement to get week number from particular date as per indian financial

year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.
But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,

Date week_no
apr 01 to 07 1
apr 08 to 14 2
apr 15 to 21 3
.
.
.
.
.
Next year mar - 31 n

Please somebody help me how to do this,am waiting for your reply.
Thanks in advance.

Report message to a moderator

Re: Display Week number as per financial year(Apr - Mar) [message #572269 is a reply to message #572268]

Sun, 09 December 2012 12:43

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just use TO_CHAR(ADD_MONTHS(your_date,-4),'w'). For example:

with t as (
           select date '2012-04-01' dt from dual union all
           select date '2012-04-08' from dual union all
           select date '2012-04-15' from dual
          )
select  dt,
        to_char(add_months(dt,-4),'w') week
  from  t
/

DT        W
--------- -
01-APR-12 1
08-APR-12 2
15-APR-12 3

SQL>

SY.

Report message to a moderator

Re: Display Week number as per financial year(Apr - Mar) [message #572270 is a reply to message #572268]

Sun, 09 December 2012 12:57

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Sat, 24 March 2012 16:20

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Also can you feedback to those that help you.
Do not they deserve your thanks?
Or do you think they owe you their time and knowledge?

Regards
Michel

Report message to a moderator

Re: Display Week number as per financial year(Apr - Mar) [message #572319 is a reply to message #572270]

Mon, 10 December 2012 10:21

rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU

Junior Member

Thanks for your reply but my problem not solved yet.
What I want is,week number selection as per financial year of India.
ie, April to next year March.
Say for example my current financial year is 01/April/2011 till 31/March/2012.
If I use the code you reply to get week number for a date 6/jan/2012
(Next year January for the fin year 2011/2012) the result has to be week no 41 but the code does not display the week number as I required.

The code shown below,

with t as (
select date '2012-01-01' dt from dual union all
select date '2012-01-08' from dual union all
select date '2012-01-15' from dual
)
select dt,
to_char(add_months(dt,-4),'w') week
from t

Output:

Dt Week
4/1/2012 1
4/8/2012 2
4/15/2012 3

But I want the result shown below

Dt Week
4/1/2012 40
4/8/2012 41
4/15/2012 42

...
...
...
...
...
Till the financial year ends march/2012(as the financial year starts apr/2011 to mar/2012).
So please solve my problem.Again I am waiting for your reply.

Report message to a moderator

Re: Display Week number as per financial year(Apr - Mar) [message #572322 is a reply to message #572319]

Mon, 10 December 2012 11:09

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Sun, 09 December 2012 19:57

From your previous topic:

Michel Cadot wrote on Sat, 24 March 2012 16:20
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Also can you feedback to those that help you.
Do not they deserve your thanks?
Or do you think they owe you their time and knowledge?

Regards
Michel

Quote:

Thanks for your reply but my problem not solved yet.

And it will not be untill you do comply the rules.

Quote:

So please solve my problem.Again I am waiting for your reply

So help us to help you and post what is required and requested.

And do not forget to FIRST review your previous topics to thank those who helped you.

Regards
Michel

[Updated on: Mon, 10 December 2012 11:10]

Report message to a moderator

Previous Topic:	CLOB to Table
Next Topic:	Gathering Stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 25 10:15:53 CDT 2025