Re: Vs: Function to calculate weekdays left in a month
- From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
- To: sjb1970@xxxxxxxxxxxxxx
- Date: Tue, 5 Aug 2008 08:57:48 +1200
Hi Steve,
You need to be careful with the 'D' if you're working in different
NLS_TERRITORYs:
alter session set NLS_TERRITORY = 'AMERICA';
select to_char(to_date('02-Aug-2008','DD-Mon-YYYY'),'D') from dual;
--> 7
alter session set NLS_TERRITORY = 'NEW ZEALAND';
select to_char(to_date('02-Aug-2008','DD-Mon-YYYY'),'D') from dual;
--> 6
Cheers
Tony
PS A bit long winded, but this works in English:
select sum(weekday) week_days, sum(weekend) weekend_days
from (select case
when d in ('mon', 'tue', 'wed', 'thu', 'fri') then
1
end weekday,
case
when d in ('sat','sun') then
1
end weekend
from (select to_char(sysdate + (level - 1), 'dy') d
from dual
connect by (level - 1) <= last_day(sysdate) - sysdate));
Other related posts:
