Re: SQL, return last sundays date...

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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;