Re: SQL, return last sundays date...

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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;