Home » RDBMS Server » Server Administration » Excluding saturday & Sunday
Excluding saturday & Sunday [message #370644] Fri, 14 January 2000 02:25 Go to next message
Rajaganapathy Mohan
Messages: 5
Registered: January 2000
Junior Member
Hi everybody,

I have a small problem in oracle SQL.I want to find the
difference between sysdate and date of joining stored in the emp table but this must exclude saturday and sunday......For (eg)

select sysdate - doj from emp ( but this must exclude
saturday and sunday....)

Rgds,
Mohan
Re: Excluding saturday & Sunday [message #370645 is a reply to message #370644] Fri, 14 January 2000 06:38 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Mohan,
Here's a quick and dirty version of a function that may solve your problem - you may want to add some error checking (make sure the input date is not in the future, for example) but it should give you the general idea.
Hope it helps,
Paul

CREATE OR REPLACE FUNCTION BUS_DAYS(IN_DATE DATE)
RETURN NUMBER IS BDAYS NUMBER(10);
v_count NUMBER(10) := 0;
v_date DATE;
v_done VARCHAR2(1);
BEGIN
v_date := IN_DATE;
v_done := 'N';
WHILE v_done = 'N' LOOP
IF to_char(v_date,'DAY') in ('SATURDAY', 'SUNDAY') THEN
v_date := v_date + 1;
ELSE
v_count := v_count + 1;
v_date := v_date + 1;
END IF;
IF trunc(v_date) > trunc(SYSDATE) THEN
v_done := 'T';
END IF;
END LOOP;
BDAYS := v_count;
RETURN BDAYS;
END;
Re: Excluding saturday & Sunday [message #370646 is a reply to message #370645] Fri, 14 January 2000 06:48 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Mohan,
Little too quick & dirty, forgot to trim the day - this will work
CREATE OR REPLACE FUNCTION BUS_DAYS(IN_DATE DATE)
RETURN NUMBER IS BDAYS NUMBER(10);
v_count NUMBER(10) := 0;
v_date DATE;
v_done VARCHAR2(1);
BEGIN
v_date := IN_DATE;
v_done := 'N';
WHILE v_done = 'N' LOOP
IF rtrim(to_char(v_date,'DAY')) in ('SATURDAY', 'SUNDAY') THEN
v_date := v_date + 1;
ELSE
v_count := v_count + 1;
v_date := v_date + 1;
END IF;
IF trunc(v_date) > trunc(SYSDATE) THEN
v_done := 'T';
END IF;
END LOOP;
BDAYS := v_count;
RETURN BDAYS;
END;
/
Regards,
Paul
Re: Excluding saturday & Sunday [message #370652 is a reply to message #370646] Sun, 16 January 2000 23:08 Go to previous messageGo to next message
Rajaganapathy Mohan
Messages: 5
Registered: January 2000
Junior Member
Hai Paul,
That was a great reply from you.Thanks for the reply.But there are two things.
1)I think we have to check up whether the in_date is greater than sysdate before entering the loop.
(ie) this part
IF trunc(v_date) > trunc(SYSDATE) THEN
v_done := 'T';
END IF;

2)Can this be done in SQL without using a fuction

Rgds,
Mohan
Re: Excluding saturday & Sunday [message #370656 is a reply to message #370652] Mon, 17 January 2000 08:14 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Mohan,
The answer depends on what you are trying to accomplish. If you only want to see records where the date you are testing is <= A TO ACCOMPLISH SYSDATE, - CAN AS THIS CODE WHERE YOU CLAUSE IN:

SELECT whatever_else_you_want, bdays(your_date)
FROM your_table
WHERE TRUNC(your_date) <= TRUNC(SYSDATE);

The WHERE clause excludes all records where date If you need to see all the records, and just flag those where date > sysdate, you could do something like this:

SELECT whatever_else_you_want,
DECODE(FLOOR((TRUNC(sysdate) - TRUNC(date)) / 10000),-1,'T',TO_CHAR(bdays(date)))
FROM your_table;

DECODE is the SQL*PLUS equivalent of an IF, the only restriction is that it can only compare for equality - so we divide the number of days returned by a large number, assuring a negative
fraction if date > sysdate. FLOOR then returns the next smallest integer (in this case -1 if date > sysdate). DECODE then interprets the returned value, if -1 you display 'T
Re: Excluding saturday & Sunday [message #370662 is a reply to message #370652] Mon, 17 January 2000 21:05 Go to previous message
Rajaganapathy Mohan
Messages: 5
Registered: January 2000
Junior Member
Hai Paul,
That was a great explanation from your side.Thanks
for the help

Rgds,
Mohan
Previous Topic: Connecting to a remote db within PL/SQL Procedure
Next Topic: not a single-group group function
Goto Forum:
  


Current Time: Sat Oct 31 20:06:19 CDT 2020