Re: SQL, return last sundays date...

Hi Gerald,

What date do you want if today is Sunday? Do you want today's date or a
week ago? If the latter, this will work:

select *
from( values(
case
when dayofweek(current_date) > 1 then current_date -
(DAYOFWEEK(current_date) - 1) DAYS
else current_date - 7 days
end)) t ("Last Sunday") ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144

Please consider sponsoring me for the Friends For Life Bike Rally
<https://secure.e2rm.com/registrant/FundraisingPage.aspx?SID=5323935&LangPref=en-CA>
in support of the Toronto People with AIDS Foundation.


On 29 June 2015 at 16:14, Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
wrote:

I have a table of week end dates, "most" of them are Saturday, so I could
just say (date - 6),
but some of my days of week vary, start of year, end of year...

what SQL do I use to return the previous closest Sunday?

(bonus points to help me when I need to start on "01-01-xxxx", not Sunday)


this (almost) works, except for beginning and end of year:

SELECT idate.idate(enddt) as end_date, (idate.idate(enddt)-6 days) as
start_date
from week_table;