SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS WeekStartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS WeekEndDate;
SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekStartDate,
DATEADD(dd, 7 - DATEPART(dw, GETDATE()), GETDATE()) AS WeekEndDate;
Output:
DayName WeekStartDate WeekEndDate
Tuesday 2015-03-30 00:00:00.000 2015-04-05 00:00:00.000
DayName WeekStartDate WeekEndDate
Tuesday 2015-03-29 19:13:17.477 2015-04-04 19:13:17.477