Date calculation 3

[Phishy]

Hi,

Is it possible to calculate the number of days between two dates and substract all the weekend days ?

select appdate - sysdate
from dual

Help please !

Thanks a lot !

 

[kindus]

I donot think there is anything in-built for that. You can built a function, using following SQL features
1) select date1 - date2 from dual
This will give the number of days between two dates
2) select to_char(date1,'DAY') from dual
This will give the day of the date like 'Monday',.....

Hope it helps you.

 

[carp]

This could probably be more efficient, but here's a function that I just banged out that will do the job:

=================================
CREATE OR REPLACE FUNCTION weekdays(p_first_date DATE,
p_last_date DATE)
RETURN NUMBER AS
v_count NUMBER := 0;
v_holder DATE;
v_first_date DATE := p_first_date;
v_last_date DATE := p_last_date;

BEGIN
-- MAKE SURE THE EARLIEST DATE IS FIRST
IF (v_first_date > v_last_date) THEN
v_holder := v_first_date;
v_first_date := v_last_date;
v_last_date := v_holder;
END IF;
-- NOW COUNT THE WEEKDAYS
FOR i IN to_char(v_first_date,'J')..to_char(v_last_date,'J') LOOP
IF to_char(to_date(i,'J'),'DAY') NOT IN ('SATURDAY ','SUNDAY ') THEN
v_count := v_count + 1;
END IF;
END LOOP;
RETURN v_count;
END;
===========================

Now you can run a query like:

SQL> SELECT weekdays('23-JUN-01',sysdate) from dual;

WEEKDAYS('23-JUN-01',SYSDATE)
-----------------------------
48

 

[uparna]

Carp ,
Thanks a lot for the function.
I didnt paste the question ,even so ... I really appreciate your efforts (of creating the function ) a lot!
thank god for geniuses like you , whom we can always reach thro tek-tips ! Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[Phishy]

Thanks a lot Carp !

That works very fine... Good work.

Regards,
Phishy

 

[carp]

You're most certainly welcome!
And it should be noted that if you need a companion function to count weekend days, it can easily be done by changing "NOT IN" to "IN" (as well as changing the function name, of course!).

In fact, it might not be a bad idea to just start a collection of date-related routines as a package. You could include the weekday and weekend functions, as well as one I find very useful:

CREATE OR REPLACE FUNCTION timestamp(p_date DATE) RETURN DATE AS
BEGIN
RETURN to_char(p_date,'fmMonth ddth,fmRRRR HH24:MI:SS');
END;

I have this on every database I own simply because I'm lazy and get tired of having to to_char every date I run across.
Now I just
timestamp(column_name)
to get the complete date information.

Other functions might just return the year or day, etc. When you're done, you'll have a whole battery of date routines available. Your users will probably appreciate it too, if you set up public synonyms and grant execute on your package to public.

 

[THoey]

Carp, nice follow-up idea after the excellent code post... Terry M. Hoey

 

[sem]

Carp's procedure will work fine if your time interval is not too large. It also will fail on Oracle 8.0 because of using chars in the loop index (need to be converted).

Another idea is to use to_char(your_date,'D'), that returns a day's number in the week.
So to calculate the number of working days you may

calculate the brutto difference:
date2-date1+1 (+1 to include ends)

extract the doubled number ow full weeks :
2*floor((date2-date1)/7)

extract the number of weekends in the last incomplete week:
The least obvious part for in general it depends on NLS settings (in some countries week starts from monday and the sunday's name is in fact not sunday and Carp's method may be used only if altering session), so for the unhappy rest part of the world whose week starts from the gloomy monday or other day, assuming w1 and w2 are the order numbers of weekends we have:

round((sign(mod(date2-date1,7)-mod(w1-to_char(date1,'D'),7))+1)/2) /*obfuscated if p1>=p2 then 1 else 0, checks whether w1 is between the end of the last full week and day2*/
+
round((sign(mod(date2-date1,7)-mod(w2-to_char(date1,'D'),7))+1)/2)/*the same with w2*/

And the complete formula is:

date2-date1 + 1 - 2*floor((date2-date1)/7)
- round((sign(mod(date2-date1,7)
-mod(w1-to_char(date1,'D'),7))+1)/2)
- round((sign(mod(date2-date1,7)
-mod(w2-to_char(date1,'D'),7))+1)/2)

To be more precise we have to check that date2>=date1 or refine the formula a bit. For earlier releases of Oracle (pre 7, suppose) we also have to make explicit to_number conversions.

Damned Babylon :-(

 

[carp]

I KNEW there would be a more efficient way!

As for the loop not working in 8.0 - I'm not so sure. It ran for me on an 8.0.4 instance. At any rate, if you DO run into this problem, it's overcome with the following line change:

FOR i IN to_number(to_char(v_first_date,'J'))..to_number(to_char(v_last_date,'J')) LOOP

 

[carp]

I KNEW there would be a more efficient way! Outstanding piece of work, SEM!

As for the loop not working in 8.0 - I'm not so sure. It ran for me on an 8.0.4 instance. At any rate, if you DO run into this problem, it's overcome with the following line change:

FOR i IN to_number(to_char(v_first_date,'J'))..to_number(to_char(v_last_date,'J')) LOOP

 

[sem]

Unfortunately Oracle and other software giants just claim about their care of non-English speaking countries. Even samples shipped with Oracle products often fail to work with non-default NLS settings. Every time I have to add a number of appropriate statements at the begining of each installation script working with dates or numbers. I only rely on a great number of guest programmers. Or maybe somebody invites me...

As for using char as loop index, it failed on 8.0.5, though as it works on 8.0.4 I beleive it's unpatched bug.

And again, damned, damned Babylon.