Re: SQL, return last sundays date...

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;