Wednesday, October 28, 2015

How to count the number of any day / days between two dates

Hi every one ... If we want to count the number of Saturdays and Sunday between two dates, how would we do that ? !

Dates are ( 01-Oct-2015) to (31-Oct-2015)

how to do it in SQL

SELECT TO_CHAR(dat,'DY'),COUNT(*) FROM
    (select TO_DATE('01-Oct-2015') + num dat from
    (SELECT level - 1 num
    FROM dual
    CONNECT BY level <= ABS((TO_DATE('01-Oct-2015') - TO_DATE('31-Oct-2015'))-1)))
    WHERE TO_CHAR(dat,'DY') IN ('SAT','SUN')
    GROUP BY TO_CHAR(dat,'DY')

This query helps me a lot of in my requirement, hope it helps you and fulfill your requirement as well. Also, i if you have any best solution do not forget to share as well.
Regards



No comments:

Post a Comment