Re: SQL, return last sundays date...

Gerald,

I think this will give you what you want.
When Monday to Saturday, returns the previous Sunday. When Sunday, returns
the current date.

select *
from( values(
case
when dayofweek(current_date) > 1 then current_date - (DAYOFWEEK(current_date)
- 1) DAYS
else current_date
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 17:07, Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
wrote:

I will want the current day (week is 01/01/ through 01/01)... (as my file
has the previous week ending as 12/31)

On Mon, Jun 29, 2015 at 3:51 PM, Glenn Gundermann <
glenn.gundermann@xxxxxxxxx> wrote:

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;