What about *last* Sunday?

An earlier (much earlier) post covered how to detect the next upcoming date that is a certain day of the week, and I just had a similar request for the last date it was a certain day of the week and cooked up this:

CREATE FUNCTION dbo.cust_funct_last_weekday
(@weekday INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @date DATETIME
SET @date=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

IF DATEPART(dw,@date)=@weekday
SET @date=DATEADD(day,-1,@date)
WHILE DATEPART(dw,@date)<>@weekday
SET @date=DATEADD(day,-1,@DATE)

RETURN @date
END

Note that this function accepts an integer and returns a date, so you specify the numeric day of the week (e.g. 1 = Sunday, 2 = Monday...7 = Saturday, although this is actually just the US default for a SQL setting called @@DATEFIRST (MSDN page)), and it returns the last Date that was that day of the week. If you specify the same day of the week as today, it returns the same day of the week from last week (i.e. it will not return today). Also, note that it returns the date with a midnight (00:00:00) time component, so you might need to massage your comparison to remove specific times, e.g. "WHERE core_occurrence.occurrence_start_time = dbo.cust_funct_last_weekday(1)" becomes "WHERE DATEADD(day,DATEDIFF(day,0,core_occurrence.occurrence_start_time),0) = dbo.cust_funct_last_weekday(1)".

This can be useful for some automated reporting, such as "I want a report in my inbox Monday morning showing our attendance numbers for Sunday's services" or "How many kids attended the Wednesday night service?", and a host of other "last N-day" questions (possibly even in lists...).