This is my first post in a long while. Recently, I have some more free time, so I’ll try to post more frequently. For this post, I will solve a variation of a problem that that I have encountered many times.

Requirements

Write a T-SQL function that takes a date input and returns the most recent date that was Sunday. If the input date was a Sunday then return the same date. The solution should work for all versions of SQL Server currently supported by Microsoft.

Solution 1

-- GetWeekStart returns the most recent Sunday from a given date
CREATE FUNCTION [dbo].[GetWeekStart](
	@Date DATE
	)
RETURNS DATE
AS
BEGIN
	-- determine the date Sunday is on by finding the numerical
	-- day of the week and subtracting the difference in days to find Sunday
	RETURN DATEADD(DAY, - DATEPART(WEEKDAY, @Date) + 1, @Date);
END;

This solution works… if you live in North America or have a system date setting that treats Sunday as the first day of the week. This will return the first day of the week for a given day in your locale, so it may be a good solution if you need to find the system defined first day of the week. In our case, we have a requirement that we always return the date that is Sunday. The behavior can also be changed per session using SET DATEFIRST 7 but would require the client alter it’s setting before calling the function so it’s not reliable.

Solution 2

-- GetWeekStart returns the most recent Sunday from a given date
CREATE FUNCTION [dbo].[GetWeekStart](
	@Date DATE
	)
RETURNS DATE
AS
BEGIN
	-- get the last 7 days and select the one that is Sunday
	SELECT @Date = d
	FROM (
		VALUES (@Date),
			(DATEADD(DAY, -1, @Date)),
			(DATEADD(DAY, -2, @Date)),
			(DATEADD(DAY, -3, @Date)),
			(DATEADD(DAY, -4, @Date)),
			(DATEADD(DAY, -5, @Date)),
			(DATEADD(DAY, -6, @Date))
	) AS w(d)
	WHERE DATENAME(WEEKDAY, d) = 'Sunday';
	
	RETURN @Date;
END;

Okay, this solution is kinda silly, but I was in a silly mood when I came up with it. Also, it only works if your language setting for the system is English so probably not that reliable. But hey, at least it doesn’t care about the DATEFIRST setting. :P

Even though this solution is garbage, one thing I like about this technique is using a VALUES expression to derive a dynamic table to select from. I think it’s far too common for database developers to overlook the power and utility of VALUES expressions. I should probably explore this more in a future post.

Solution 3

-- GetWeekStart returns the most recent Sunday from a given date
CREATE FUNCTION [dbo].[GetWeekStart](
	@Date DATE
	)
RETURNS DATE
WITH SCHEMABINDING
AS
BEGIN
	-- find the number of days that have elapsed from a known Sunday modulo 7
	-- and subtract that many days to find Sunday
	RETURN DATEADD(DAY, - DATEDIFF(DAY, CONVERT(DATE, '0001-01-07T00:00:00', 112), @Date) % 7, @Date);
END;

This solution works for all dates between 0001-01-07 and 9999-12-31 regardless of any localization setting. Bonus points for also being deterministic which wasn’t a requirement but still a nice design goal.

By the way, the reason this is deterministic is because I am converting a string literal that is ISO 8601 formatted and making sure to tell the CONVERT function that it’s format is 112 (which is ISO 8601). If I had used a localized date time string or relied on implicit type conversion this function wouldn’t be deterministic. I could have also used an ODBC date literal to avoid having to cast a string as date but {d’1753-01-07′} is the earliest date that could have been used in that case.