There was an error in this gadget

Search This Blog

Wednesday, January 5, 2011

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.

No comments:

Post a Comment