Home » SQL & PL/SQL » SQL & PL/SQL » How To Get Week Number From specified date.
How To Get Week Number From specified date. [message #443277] Sun, 14 February 2010 15:27 Go to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello All,

i am working on a sql query for grouping of data based on weeks.

However the Grouping of data is not based on the calender Week Which means For example.

From_date -10/01/2010
to_date -10/02/2010

If we are calculating the week on the from_date it will give you calender week.
from the above from_date (10/01/2010) it is week2 from the calender. .

What i require is (From_date+ 1st 7 days ) Should be the week1

(From_date+ 2nd 7 days ) should be week2.

Please look into the Below sample report data.

Report Output Should be
from_date week
10/01/2010 Week1
11/01/2010 week1
12/01/2010 week1
15/01/2010 week1
17/01/2010 week1

18/01/2010 week2
19/01/2010 week2

Please let me know your valuable inputs in the timely manner.
Re: How To Get Week Number From specified date. [message #443278 is a reply to message #443277] Sun, 14 February 2010 18:23 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why is it you refuse to follow Posting Guidelines?

Why is it you have not posted any actual SQL?

Below shows how to generate week numbers based upon date range.
You are free to manipulate, massage or mangle the SQL below to meet your specific requirements.

SQL> @cal5
SQL> select trunc((dte-to_date('&BEG_DATE','YYYY-MM-DD'))/7)+1 Week ,dte
  2  from  (select to_date('&&BEG_DATE','YYYY-MM-DD') +level-1	dte from   dual
  3  	    connect by level <= TO_DATE('&&END_DATE','YYYY-MM-DD')+1 - to_date('&&BEG_DATE','YYYY-MM-DD'))
  4  /
old   1: select trunc((dte-to_date('&BEG_DATE','YYYY-MM-DD'))/7)+1 Week ,dte
new   1: select trunc((dte-to_date('2010-01-13','YYYY-MM-DD'))/7)+1 Week ,dte
old   2: from  (select to_date('&&BEG_DATE','YYYY-MM-DD') +level-1  dte from   dual
new   2: from  (select to_date('2010-01-13','YYYY-MM-DD') +level-1  dte from   dual
old   3:	connect by level <= TO_DATE('&&END_DATE','YYYY-MM-DD')+1 - to_date('&&BEG_DATE','YYYY-MM-DD'))
new   3:	connect by level <= TO_DATE('2010-01-31','YYYY-MM-DD')+1 - to_date('2010-01-13','YYYY-MM-DD'))

      WEEK DTE
---------- ---------
	 1 13-JAN-10
	 1 14-JAN-10
	 1 15-JAN-10
	 1 16-JAN-10
	 1 17-JAN-10
	 1 18-JAN-10
	 1 19-JAN-10
	 2 20-JAN-10
	 2 21-JAN-10
	 2 22-JAN-10
	 2 23-JAN-10

      WEEK DTE
---------- ---------
	 2 24-JAN-10
	 2 25-JAN-10
	 2 26-JAN-10
	 3 27-JAN-10
	 3 28-JAN-10
	 3 29-JAN-10
	 3 30-JAN-10
	 3 31-JAN-10

19 rows selected.

[Updated on: Sun, 14 February 2010 20:14]

Report message to a moderator

Previous Topic: Cartesian Product query
Next Topic: regexp expected
Goto Forum:
  


Current Time: Thu Feb 13 14:13:38 CST 2025