Days gap between two dates in ORACLE

To find out days gap between two dates excluding business holidays in oracle use following query.

SELECT count(dt) FROM
(SELECT TO_DATE(StartDate) + level - 1 dt FROM dual CONNECT BY level <=(TO_DATE(EndDate)-TO_DATE(StartDate)))
where to_char (dt,'D')<>1

Below written query is the example of it.

This query returns days count between 30-nov-2010 and 12-dec-2010 excluding Sundays
(1 for sunday,2 for monday and so on......)

SELECT count(dt) FROM
(SELECT TO_DATE('30-nov-2010') + level - 1 dt FROM dual CONNECT BY level <=(TO_DATE('12-Dec-2010')-TO_DATE('30-nov-2010')))
where to_char (dt,'D')<>1 

To find out dates between two dates use following query

SELECT to_char(dt,'dd/MM/yyyy') as date FROM
(SELECT TO_DATE(StartDate) + level - 1 dt FROM dual CONNECT BY level <=(TO_DATE(EndDate)-TO_DATE(StartDate)))
where to_char (dt,'D')<>1

Happy coding.