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
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