If you want to find out weekdays between two given dates then use the PL/SQL script below:
Step1: Create a function using the following script CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
RETURN NUMBER IS
totalsundays NUMBER;
totalsaturdays NUMBER;
begin
totalsundays
:= NEXT_DAY (todate - 7, 'sunday')
- NEXT_DAY (fromdate - 1, 'sunday');
totalsaturdays
:= NEXT_DAY (todate - 7, 'saturday')
- NEXT_DAY (fromdate - 1, 'saturday');
RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1);
END totworkdays;
Step2: Call the above function with the given dates as input parameters:
declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays ('01-jan-2009', '31-jan-2009');
dbms_output.put_line('Total Work Days: '||lv_tot_work_days);
end;
Step1: Create a function using the following script CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
RETURN NUMBER IS
totalsundays NUMBER;
totalsaturdays NUMBER;
begin
totalsundays
:= NEXT_DAY (todate - 7, 'sunday')
- NEXT_DAY (fromdate - 1, 'sunday');
totalsaturdays
:= NEXT_DAY (todate - 7, 'saturday')
- NEXT_DAY (fromdate - 1, 'saturday');
RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1);
END totworkdays;
Step2: Call the above function with the given dates as input parameters:
declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays ('01-jan-2009', '31-jan-2009');
dbms_output.put_line('Total Work Days: '||lv_tot_work_days);
end;
Nice coding to know about the weekdays between given dates,nice posting.blog hosting review
ReplyDeletePL/SQL scripts or tutorial for creating a function to calculate the week days between 2 given dates.
ReplyDeleteoracle ebs