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;

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.