Re: SQL, return last sundays date...

[Booth Martin <booth@xxxxxxxxxxxx>]

Here is an algorithm that I use: Remove the whole weeks, which leaves an 
offset.  Apply that offset to today's date and that should be last Sunday.

Today - (any long ago Sunday) = days(*days)
days/7 = (no one cares)
The remainder is your offset.
Today less that offset is last Sunday.

(You may have to adjust the offset by making 0=7 for when today is Sunday.)



On 6/29/2015 3:14 PM, Gerald Magnuson 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;