Home » SQL & PL/SQL » SQL & PL/SQL » Display Week number as per financial year(Apr - Mar) (Oracle 9i)
Display Week number as per financial year(Apr - Mar) [message #572268] Sun, 09 December 2012 12:01 Go to next message
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.
Re: Display Week number as per financial year(Apr - Mar) [message #572269 is a reply to message #572268] Sun, 09 December 2012 12:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2006
Registered: January 2010
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.
Re: Display Week number as per financial year(Apr - Mar) [message #572270 is a reply to message #572268] Sun, 09 December 2012 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58937
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Display Week number as per financial year(Apr - Mar) [message #572319 is a reply to message #572270] Mon, 10 December 2012 10:21 Go to previous messageGo to next message
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.
Re: Display Week number as per financial year(Apr - Mar) [message #572322 is a reply to message #572319] Mon, 10 December 2012 11:09 Go to previous message
Michel Cadot
Messages: 58937
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Fri Aug 29 11:10:38 CDT 2014

Total time taken to generate the page: 0.16243 seconds