As the title said, I want to calculate days between 2 dates and exclude weekend(Saturday and Sunday) in Oracle, I have no idea about this at all, can anyone help give an example SQL?
Asked
Active
Viewed 1,560 times
0
-
Do you mean you want to generate rows for all the days between two given dates? Also, are there multiple rows of these two dates - like row1 - d1 to d2, row2 - d3 to d4? Please post some example what you will give as input and what you want as output? Where is the input coming from? a table? is there an id column or any other unique column? – Gurwinder Singh Mar 04 '17 at 12:49
-
for example, there are start_date and end_date, I use (end_date - start_date) will get the number of days, so if the duration contains Saturday and Sunday, the result should be (days - 2) – user2575502 Mar 04 '17 at 12:56
-
Please [**edit**](http://stackoverflow.com/posts/42596211/edit) the question and add example data and result – Gurwinder Singh Mar 04 '17 at 12:57
2 Answers
1
Try this. Change start_date and end_date as per your requirement.
If you want number of weekdays, use
WITH test_data AS
(
SELECT TO_DATE('01-JAN-2014','DD-MON-YYYY') AS start_date,
TO_DATE('31-DEC-2014','DD-MON-YYYY') AS end_date
FROM dual
),
all_dates AS
( SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
FROM test_data td
CONNECT BY td.start_date + LEVEL-1 <= td.end_date)
select count(*) from all_dates
WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat')
If you want the dates displayed, in the select clause, use
select week_day from all_dates
How it works:
- It start adding 1 day to start_date until you reach
end_date, by usingconnect by/levelin Oracle. - It removes the days which are
saturdayorsunday
PS: Refered this answer for it
0
How about simplifying
"Select * from emp where hiredate between To_date(f_date,'dd-mm-yyyy) and to_date(t_date, 'Dd-mm-yyyy') and to_char(hiredate,dy) not in('sat','sun')
