find out the week of the year 1

[anju123]

Hi friends,
I need your help. I need to find out the week of the year. Does anyone have any query for that or know how to calculate it. For example if user passes any date then that query should return the week of the year.
Thanks in advance.

 

[SantaMufasa]

Anju,

Oracle is pretty amazing with its date manipulation capabilities. If you want to return the week of the year, you can use the 'ww' mask of the to_char function. For example, a week ago should appear as Week 52:

select to_char(sysdate-7,'ww') from dual;

52

1 row returned.

You may also use the following character masks for these results:

YEAR  = Year, spelled out 
YYYY  = 4-digit year 
YYY   = last 3 digits of the year
YY    = last 2 digits of the year
Y     = last digit of the year
IYY   = last 3 digits of ISO year
IY    = last 2 digits of ISO year
I     = last digit of ISO year
IYYY  = 4-digit year based on the ISO standard 
Q     = Quarter of year (1, 2, 3, 4; JAN-MAR = 1)
MM    = Month number (01-12; JAN = 01). 
MON   = 3-char (upper case) abbreviated name of month
Mon   = 3-char (init caps) abbreviated name of month
mon   = 3-char (lower case) abbreviated name of month
MONTH = Fully spelled upper-case name of month, padded with blanks to length of 9 characters
Month = Same a above, init caps
month = Same a above, lover case
RM    = Roman numeral month (I-XII; JAN = I). 
WW    = Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. 
W     = Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. 
IW    = Week of year (1-52 or 1-53) based on the ISO standard. 
D     = Day of week (1-7). 
DAY   = Fully spelled upper-case name of day
Day   = same as above, init caps
day   = same as above, lower-case
DD    = Day number of month (1-31)
Ddsp  = Cardinal day number spelled, init caps (22 = "Twenty-Two")
Ddspth = Ordincal Day number spelled, init caps (22 = "Twenty-Second")
DDD   = Day of year (1-366). 
DY    = 3-char upper-case abbreviated name of day
Dy    = same as above, init caps
dy    = same as above, lower-case
J     = Julian day; the number of days since January 1, 4712 BC. 
HH    = Hour of day (1-12). 
HH24  = Hour of day (0-23). 
MI    = Minute (0-59). 
SS    = Second (0-59). 
SSSSS = Seconds past midnight (0-86399). 
FF    = Fractional seconds

Note 1: You can add "sp" or "spth" to any of the above numeric masks to result in either spelled cardinal numbers or spelled ordinal numbers, respectively.

Note 2: Use "fm" to toggle extra-space suppression/insertion in the to_char masks.

Note 3: You can "ALTER SESSION SET NLS_LANGUAGE = SPANISH;" for Day and Month spellings in Spanish. You may also use many other languages, instead of Spanish: such as german, french, lithuanian, italian, ukrainian, et cetera. Example:
[code]
ALTER SESSION SET NLS_LANGUAGE = SPANISH;

Session altered.

select to_char(sysdate,'fmDay, Month dd, yyyy') from dual;

Lunes, Enero 3, 2005

1 row selected.

Let us know if this resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 04:10 (04Ene05) UTC (aka "GMT" and "Zulu"),
@ 21:10 (03Ene05) Mountain Time

 

[RhythmAddict112]

Im sure that will come in handy for me.have a star.

 

[anju123]

Hi Musafa,
Thanks for your help but i need my week to start from sunday instead of starting from monday.
ex: select to_char(to_date('26-dec-2004'),'iw') from dual;
actually comes as 52nd week where as i want it to be 53 week.
i have written a code by myself, it works fine for all but fails for 1st jan 2005

select to_char(floor(to_number( to_char(to_date('26-dec-2004'),'DDD') +
(MOD(to_number(to_char(trunc(to_date('26-dec-2004'), 'YYYY'),'D')),7) -2) )/7)+1)
from dual

can you help me please.

thansk in advance.

 

[TheRambler]

Hi,

Here is a way to calculate it. You will have to adapt it to suit your needs, maybe adding one to the Julian day number, sorry I can't provide more help. Good luck.

 

[SantaMufasa]

Anju,

Actually, Oracle does not base its "Week of the Year" algorithm on either Sunday or Monday...Oracle bases "Week of the Year" on whatever day "January 1" falls upon. So, for any year, regardless of day of the week, January 1-7 is week 1, January 8-14 is week 2, ... December 30 is the last day of week 52, and December 31 is the first, last, and only day of week 53 in non-leap years.

Now, if what you want is to count "full weeks" only (meaning that Sunday, January 2, 2005 began "Week 1" of 2005) then we can write an algorithm for that, but you are left with the logical dilemma of what to do with days of the year which precede the First Sunday of the year.

Let me know what you want your "Week of the Year" algorithm to do (including when Week 1 begins and what to do about days of the year that precede Week 1 of the same calendar year) and I'm sure we can write a routine for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:09 (11Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:09 (11Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[anju123]

Hi Musafa,
Thanks for your quick response.
i have written a code by myself, it works fine for all year but failed for last week of 2004 and 1st week of Jan.
Actually this function is calculation from 26th Dec till 1st Jan as 1week (which is correct) but it’s again calculating from 2nd jan till 8th Jan as 1st week whereas I want it to be 2nd week. Basically this function is not going to work properly for this whole year after that its working fine.

Did you get my point?? Take a look and see if you can help me.

FUNCTION WEEK_OF_YEAR ( Date_i IN DATE) return NUMBER IS
L_week NUMBER;
BEGIN
L_week := to_char(floor(to_number(to_char(Date_i,'DDD')+
(MOD(to_number(to_char(trunc(Date_i, 'YYYY'),'D')),7)-2))/7)+1);
IF L_week = 53 THEN
L_week := 1 ;
END IF;
Return (L_week);
END;

Thanks in advance.

 

[SantaMufasa]

Anju,

I believe I understand your objectives and premises now for your "Week of the Year":

1) The first week of the year begins with the Sunday of the week during which January 1 occurs. Therefore, Week 1 of any year might include (at earliest) Sunday, December 26 (as with this year, 2005).

2) Also as with 2005, there can be 53 weeks in the year since the last day of 2005 is Saturday, December 31.

Given these premises, I believe the following code gives you the correct Week of the Year for each and every year:

Section 1 -- Function definition:

create or replace FUNCTION WEEK_OF_YEAR (Date_i IN DATE) return NUMBER IS
   offset_factor number := to_char(trunc(Date_i,'YYYY'),'D')-1;
   offset_date   date;
   week_num      number;
begin
   offset_date  := Date_i+offset_factor;
   week_num := to_char(offset_date,'WW');
   if to_char(offset_date+1,'YYYY')<>to_char(date_i,'YYYY')
      and to_char(last_day(date_i),'D')=7 then
         week_num := 53;
   elsif week_num > 52 then
         week_num := 1;
   end if;
   return week_num;
end;
/

Section 2 -- Sample invocations:

set pagesize 50
col a heading "Original Dates" format a29
col b heading "Week|Number" format a9
select to_char(dates,'Day, Month dd, yyyy')a,
       'Week: '||substr('  '||week_of_year(dates),-2)b
from anju;


                              Week
Original Dates                Number
----------------------------- ---------
Sunday   , December  26, 2004 Week:  1
Friday   , December  31, 2004 Week:  1
Saturday , January   01, 2005 Week:  1
Sunday   , January   02, 2005 Week:  2
Saturday , December  24, 2005 Week: 52
Sunday   , December  25, 2005 Week: 53
Saturday , December  31, 2005 Week: 53
Sunday   , January   01, 2006 Week:  1
Sunday   , January   08, 2006 Week:  2
Sunday   , December  31, 2006 Week:  1
Monday   , January   01, 2007 Week:  1
Thursday , January   01, 2009 Week:  1
Sunday   , January   04, 2009 Week:  2
Saturday , January   10, 2009 Week:  2
Sunday   , January   11, 2009 Week:  3

15 rows selected.

Please thoroughly test this function and let us know if it resolves your need in all cases.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 02:33 (12Jan05) UTC (aka "GMT" and "Zulu"),
@ 19:33 (11Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[anju123]

Thanks a lot for your response!