Sunitha Sunitha - 1 year ago 119
SQL Question

how to subtract the Sundays from no of days in a month

How to find out a Sundays in a month?

please help me on this..

no of Sundays : 4 for current month and i need to subtract these count from the days in a month

:: days in a month -sundays

If i pass the dates in between from and to i need to get the count of sundays for that period..

Many thanks for your help.


Answer Source

Try this:

select to_char(last_day(sysdate),'dd') -
    -next_day(trunc(sysdate,'mm')-1,'sunday'))/7+1) as result
    from dual

For count dates between a period you can do this:

SELECT (next_day(TRUNC(TO_DATE('28/09/2014','DD/MM/YYYY')),'sunday') -
    next_day((trunc(TO_DATE('13/09/2014','DD/MM/YYYY'))),'sunday')-7)/7+1 AS RESULT
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download