tsql, last week start and end date

gistfile1.sql

-- fetches the last week date range (sunday to sunday)
-- for the specified time zone offset

DECLARE @OffsetMinutes AS SMALLINT = +330

DECLARE @UtcDate AS DATETIME = GETUTCDATE()
DECLARE @LocalDate AS DATETIME = DATEADD(MINUTE, @OffsetMinutes, @UTCDATE)

DECLARE @LastSunday AS DATETIME = CONVERT(VARCHAR(4), YEAR(@LocalDate))
	+ '-' + CONVERT(VARCHAR(2), MONTH(@LocalDate))
	+ '-' + CONVERT(VARCHAR(2), DAY(@LocalDate))

WHILE (DATENAME(WEEKDAY, @LastSunday) <> 'Sunday')
	SET @LastSunday = DATEADD(DAY, -1, @LastSunday)

DECLARE @StartDate AS DATETIME = DATEADD(DAY, -7, @LastSunday)
DECLARE @EndDate   AS DATETIME = DATEADD(DAY,  7, @StartDate)  -- the code is intentional to highlight the range of 7 days