week of year calc [message #39753] |
Wed, 07 August 2002 07:22 |
ron
Messages: 50 Registered: July 1999
|
Member |
|
|
hello
I am doing the following SQL
select to_char((calendar_date), 'ww'), calendar_date, day_of_week from calendar
I am getting weeks 1-53 for a given year however at
however this returns week as 7 days regardless of the
day of the week.
I need the saturday to sunday week.
dec 31 1995 - jan 6 1996 need to be either week 53 of 1995 or week 1 of 1995
Now I get DEC 31 1995 is week 53 and Jan 1 1996 -Jan 7 1995 week 1
TIA
|
|
|
Re: week of year calc [message #39754 is a reply to message #39753] |
Wed, 07 August 2002 09:01 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
SQL> SELECT DECODE(TO_CHAR(SYSDATE,'DY'),'SAT',SYSDATE,NEXT_DAY(SYSDATE-7,'SATURDAY'))
2 week_starts,
3 DECODE(TO_CHAR(SYSDATE,'DY'),'SUN', SYSDATE,
4 NEXT_DAY(SYSDATE,'SUNDAY')) week_ends
5 FROM DUAL;
WEEK_STARTS WEEK_ENDS
----------- -----------
03-AUG-2002 11-AUG-2002
|
|
|
the same with week# [message #39755 is a reply to message #39753] |
Wed, 07 August 2002 09:08 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
SQL> SELECT DECODE(TO_CHAR(SYSDATE,'DY'),'SAT',SYSDATE,NEXT_DAY(SYSDATE-7,'SATURDAY'))
2 week_starts,
3 DECODE(TO_CHAR(SYSDATE,'DY'),'SUN', SYSDATE,
4 NEXT_DAY(SYSDATE,'SUNDAY')) week_ends,
5 TO_CHAR(SYSDATE,'IW') Num
6 FROM DUAL;
WEEK_STARTS WEEK_ENDS NU
----------- ----------- --
03-AUG-2002 11-AUG-2002 32
|
|
|