How 2 Manipulate SYSDATE to return Dates of All Sundays in a Month?

[acur8dba]

Hi,

How can I manipulate sysdate to return all the dates of the Sundays of the month in the format YYYY-MM-DD HH24:MI:SS? I need to do this dynamically, no matter when I run this.

Thanx

[Shestakov]

select *
from ( select to_char(last_day( add_months (sysdate, -1)) + rownum,'YYYY-MM-DD hh24:mi:ss') "Date",
to_char(last_day( add_months (sysdate, -1)) + rownum,'Day') "Week Day",
to_char(last_day( add_months (sysdate, -1)) + rownum,'d') wDay
from all_objects
where rownum between to_char(last_day( add_months (sysdate, -1)) + 1,'dd')
and to_char(last_day(sysdate),'dd')
) where wDay = 1;

-- orwDay = 7 depend from NLS setting

Date Week Day W
------------------- --------- -
2002-03-03 09:36:05 Sunday 1
2002-03-10 09:36:05 Sunday 1
2002-03-17 09:36:05 Sunday 1
2002-03-24 09:36:05 Sunday 1
2002-03-31 09:36:05 Sunday 1