Yet another way:
Select ...
Case When DayOfWeek_ISO(YourDate) <= DayOfYear(YourDate)
then Next_Day(YourDate - 8 days, 'SUN')
else Last_Day(YourDate - 1 Month) + 1 Day End
From ...
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Glenn Gundermann
Gesendet: Monday, 29.6 2015 23:43
An: Midrange Systems Technical Discussion
Betreff: Re: SQL, return last sundays date...
select *
from( values(
case
when dayofweek(current_date) = 1 then current_date
when dayofweek(current_date) > 1 and year(current_date -
(DAYOFWEEK(current_date)
- 1) DAYS) = year(current_date) then current_date - (DAYOFWEEK(current_date)
- 1) DAYS
else date(year(current_date) concat '-01-01')
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&LangPre
f=en-CA>
in support of the Toronto People with AIDS Foundation.
On 29 June 2015 at 17:29, Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
wrote:
yes. that is what I need...
On Mon, Jun 29, 2015 at 4:28 PM, John Yeung
<gallium.arsenide@xxxxxxxxx>
wrote:
On Mon, Jun 29, 2015 at 4:54 PM, Gerald Magnuson
<gmagqcy.midrange@xxxxxxxxx> wrote:
now I just need to figure out how to get the beginning of week,
when
end
is
< 01-07...
Like Charles, I don't know what you mean by this.
But I will take a stab. My guess is that when the "input date" is
too close to the beginning of the year (so that there is no previous
Sunday in that year), you want to instead return Jan 1 of that year.
John Y.
--
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.
midrange.com
