Home » SQL & PL/SQL » SQL & PL/SQL » Get the week of the Year (First week with starting with first SUNDAY of year) (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Get the week of the Year (First week with starting with first SUNDAY of year) [message #570423] Fri, 09 November 2012 03:54 Go to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

I want to get the week of the year.

Conditions are:

1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013)
2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)

At many place I found the below solution:

select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;

But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).

Please help.

Thanks,
Manu
Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570426 is a reply to message #570423] Fri, 09 November 2012 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a first step, you should find the rest by yourself:
SQL> with 
  2    dates as ( 
  3      select add_months(trunc(sysdate,'YEAR'),12)-5+level-1 dat
  4      from dual
  5      connect by level <= 20
  6    )
  7  select to_char(dat,'Dy DD-MON-YYYY') dat, 
  8         1+floor((dat-next_day(trunc(dat,'year')-1,'Sunday'))/7) wk
  9  from dates
 10  /
DAT                     WK
--------------- ----------
Thu 27-DEC-2012         52
Fri 28-DEC-2012         52
Sat 29-DEC-2012         52
Sun 30-DEC-2012         53
Mon 31-DEC-2012         53
Tue 01-JAN-2013          0
Wed 02-JAN-2013          0
Thu 03-JAN-2013          0
Fri 04-JAN-2013          0
Sat 05-JAN-2013          0
Sun 06-JAN-2013          1
Mon 07-JAN-2013          1
Tue 08-JAN-2013          1
Wed 09-JAN-2013          1
Thu 10-JAN-2013          1
Fri 11-JAN-2013          1
Sat 12-JAN-2013          1
Sun 13-JAN-2013          2
Mon 14-JAN-2013          2
Tue 15-JAN-2013          2

Regards
Michel
Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570438 is a reply to message #570426] Fri, 09 November 2012 10:10 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Is that correct approach?

with
dates as (
select (to_date('01-MAY-2013','DD-MON-YYYY')) dat from dual)
select to_char(dat,'Dy DD-MON-YYYY') dat,
decode(floor((dat-next_day(trunc(dat,'year')-1,'Sunday'))/7)+1,0,
floor(((trunc(dat,'year')-1)-next_day(trunc((trunc(dat,'year')-1),'year')-1,'Sunday'))/7)+1
,floor((dat-next_day(trunc(dat,'year')-1,'Sunday'))/7)+1) wk
from dates
/

Regards,
Manu

[Updated on: Fri, 09 November 2012 10:18]

Report message to a moderator

Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570475 is a reply to message #570438] Sat, 10 November 2012 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is not formatted.

Regards
Michel
Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570479 is a reply to message #570438] Sat, 10 November 2012 16:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
manubatham20 wrote on Fri, 09 November 2012 11:10
Is that correct approach?


No. Since your year not necessarily starts January 1, you need to check if date in question belongs to "your current year" or to "your previous year". Also, using NEXT_DAY produces NLS-dependent solution. It will not work if session date language is not american/english:

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(
---------
11-NOV-12

SQL> alter session set nls_date_language = 'french';

Session altered.

SQL> select next_day(sysdate,'Sunday') from dual;
select next_day(sysdate,'Sunday') from dual
                        *
ERROR at line 1:
ORA-01846: not a valid day of the week


SQL> 


It is better to use IW date format which is NLS independent. Anyway:

TRUNC(some_date,'YYYY') is beginning of year (January 1) some_date belongs to. Then TRUNC(TRUNC(DT,'YYYY'),'IW') is first Monday prior or equal to beginning of year (January 1) some_date belongs to. Therefore, TRUNC(TRUNC(DT,'YYYY'),'IW') + 6 is first Sunday prior or equal to beginning of year (January 1) some_date belongs to. Now we need to check is some_date occurs before first Sunday of the year. If so, some_date belongs to "your prior year". Otherwise it belongs to "your current year":

WITH DATES AS ( 
               SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12) - 5 + LEVEL - 1 DT
                 FROM  DUAL
                 CONNECT BY LEVEL <= 20
              )
SELECT  DT,
        CASE
          WHEN DT >= TRUNC(TRUNC(DT,'YYYY'),'IW') + 6 THEN TRUNC((DT - TRUNC(TRUNC(DT,'YYYY'),'IW') - 6) / 7)
          ELSE TRUNC((DT - TRUNC(ADD_MONTHS(TRUNC(DT,'YYYY'),-12),'IW') - 6) / 7)
        END + 1 WEEK
  FROM  DATES
/

DT                                    WEEK
--------- --------------------------------
27-DEC-12                               52
28-DEC-12                               52
29-DEC-12                               52
30-DEC-12                               53
31-DEC-12                               53
01-JAN-13                               53
02-JAN-13                               53
03-JAN-13                               53
04-JAN-13                               53
05-JAN-13                               53
06-JAN-13                                1

DT                                    WEEK
--------- --------------------------------
07-JAN-13                                1
08-JAN-13                                1
09-JAN-13                                1
10-JAN-13                                1
11-JAN-13                                1
12-JAN-13                                1
13-JAN-13                                2
14-JAN-13                                2
15-JAN-13                                2

20 rows selected.

SQL> -- and to show it is NLS-independent
SQL> alter session set nls_date_language = 'french';

Session altered.

SQL> WITH DATES AS ( 
  2                 SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12) - 5 + LEVEL - 1 DT
  3                   FROM  DUAL
  4                   CONNECT BY LEVEL <= 20
  5                )
  6  SELECT  DT,
  7          CASE
  8            WHEN DT >= TRUNC(TRUNC(DT,'YYYY'),'IW') + 6 THEN TRUNC((DT - TRUNC(TRUNC(DT,'YYYY'),'IW') - 6) / 7)
  9            ELSE TRUNC((DT - TRUNC(ADD_MONTHS(TRUNC(DT,'YYYY'),-12),'IW') - 6) / 7)
 10          END + 1 WEEK
 11    FROM  DATES
 12  /

DT                                      WEEK
----------- --------------------------------
27-DÉC. -12                               52
28-DÉC. -12                               52
29-DÉC. -12                               52
30-DÉC. -12                               53
31-DÉC. -12                               53
01-JANV.-13                               53
02-JANV.-13                               53
03-JANV.-13                               53
04-JANV.-13                               53
05-JANV.-13                               53
06-JANV.-13                                1

DT                                      WEEK
----------- --------------------------------
07-JANV.-13                                1
08-JANV.-13                                1
09-JANV.-13                                1
10-JANV.-13                                1
11-JANV.-13                                1
12-JANV.-13                                1
13-JANV.-13                                2
14-JANV.-13                                2
15-JANV.-13                                2

20 rows selected.

SQL>  


SY.
Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570702 is a reply to message #570479] Wed, 14 November 2012 09:34 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

Thanks for the suggestion SY.

But I read somewhere (don't remember exactly), that IW format is also NLS dependent.

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';


Will the above make any effect on IW format?

Regards,
Manu
Re: Get the week of the Year (First week with starting with first SUNDAY of year) [message #570703 is a reply to message #570702] Wed, 14 November 2012 09:56 Go to previous message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
manubatham20 wrote on Wed, 14 November 2012 10:34
But I read somewhere (don't remember exactly), that IW format is also NLS dependent.



Instead of reading "somewhere" read documentation:

IW - Same day of the week as the first day of the ISO week, which is Monday.

SY.
Previous Topic: Guide me in writing Case Statement
Next Topic: complex delete and insert (2 Merged)
Goto Forum:
  


Current Time: Wed Sep 03 01:17:55 CDT 2014

Total time taken to generate the page: 0.08760 seconds