RE: Vs: Function to calculate weekdays left in a month
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx
- Date: Mon, 4 Aug 2008 10:26:09 +0100
I think Marks solution is more elegant but an alternative would be;
IANS> var dval varchar2(9)
IANS> exec :dval := to_char(sysdate,'dd-mon-yy');
select count(*)
from
(
select tdate
from (select to_date('01-jan-01','dd-mon-yy') tdate from dual)
model
return updated rows
dimension by (tdate)
measures (1 as dummy)
rules (dummy[for tdate from to_date(:dval,'dd-mon-yy')
to last_day(to_date(:dval,'dd-mon-yy'))
increment numtodsinterval(1,'day')] = 1 )
)
where to_char(tdate,'dy') not in ('sat','sun');
COUNT(*)
----------------
20
For some reason trying to use sysdate as a bound to the for condition
raises an ORA-32626 error but its easy enough to use a biind variable.
Cheers,
Ian
|---------+----------------------------->
| | Mark.Bobak@proques|
| | t.com |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 04/08/2008 09:07 |
| | Please respond to |
| | Mark.Bobak |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: jack@xxxxxxxxxxxx, teijo.lallukka@xxxxxxxx
|
| cc: oracle-l@xxxxxxxxxxxxx
|
| Subject: RE: Vs: Function to calculate weekdays left in a month
|
>--------------------------------------------------------------------------------------------------------------|
Try this:
select sum((case trim(to_char(sysdate+level-1,'Day'))
when 'Saturday'
then 0
when 'Sunday'
then 0
else 1 end)) weekdays_left_this_month
from dual
connect by level <= trunc(last_day(sysdate))-trunc(sysdate)+1
As written, it includes the current day in the count.
Hope that helps,
-Mark
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jack van Zanen [jack@xxxxxxxxxxxx]
Sent: Monday, August 04, 2008 1:54 AM
To: Teijo Lallukka
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Vs: Function to calculate weekdays left in a month
If only it were that simple.
That gets me all the days and I am interested in the weekdays only.
Brgds
Jack
On 04/08/2008, Teijo Lallukka <teijo.lallukka@xxxxxxxx<
mailto:teijo.lallukka@xxxxxxxx>> wrote:
Hi!
try this, this can help you.
SQL> SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE Last Days Left
1 4.8.2008 8:48:16 31.8.2008 8:48:16 27
-TL
-----------------------------------------------------------------------------
Teijo Lallukka, Oracle DBA
teijo.lallukka@xxxxxxxx<mailto:teijo.lallukka@xxxxxxxx>
http://www.edita.fi
-----------------------------------------------------------------------------
www.edilex.fi<http://www.edilex.fi> | www.finlex.fi<http://www.finlex.fi> |
www.credita.fi<http://www.credita.fi>
-----------------------------------------------------------------------------
>>> "Jack van Zanen" <jack@xxxxxxxxxxxx<mailto:jack@xxxxxxxxxxxx>> 4.8.2008
8:45:39 >>>
Hi All,
I am not a programmer and before I spend a lot of time on some PL/SQL to do
this I would just like to double check if anybody on this list has created
such a function/proc already.
Brgds
--
J.A. van Zanen
--
J.A. van Zanen
--
//www.freelists.org/webpage/oracle-l
This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.
For the latest data on the economy and society consult National Statistics at
http://www.statistics.gov.uk
*********************************************************************************
Please Note: Incoming and outgoing email messages are routinely monitored for
compliance with our policy on the use of electronic communications
*********************************************************************************
Legal Disclaimer : Any views expressed by the sender of this message are not
necessarily those of the Office for National Statistics
*********************************************************************************
The original of this email was scanned for viruses by the Government Secure
Intranet virus scanning service supplied by Cable&Wireless in partnership with
MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this
email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.
Other related posts:
