| Excluding saturday & Sunday [message #370644] |
Fri, 14 January 2000 02:25  |
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  |
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 |
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
