Suppose you’re given a date and asked to retrieve data for the whole week in which the given date falls. The example code produces the Week Start Date and Week End Date:
-----------------------------------Please set your appropriate values for @REPORT_DATE and @WEEK_BEGINING DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)SELECT @REPORT_DATE = '2004-09-21T00:00:00'SELECT @WEEK_BEGINING = 'MONDAY'IF @WEEK_BEGINING = 'MONDAY' SET DATEFIRST 1 ELSE IF @WEEK_BEGINING = 'TUESDAY' SET DATEFIRST 2 ELSE IF @WEEK_BEGINING = 'WEDNESDAY' SET DATEFIRST 3 ELSE IF @WEEK_BEGINING = 'THURSDAY' SET DATEFIRST 4 ELSE IF @WEEK_BEGINING = 'FRIDAY' SET DATEFIRST 5 ELSE IF @WEEK_BEGINING = 'SATURDAY' SET DATEFIRST 6 ELSE IF @WEEK_BEGINING = 'SUNDAY' SET DATEFIRST 7 DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME--GET THE WEEK START DATESELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1) --GET THE WEEK END DATESELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)----------------------------------------------The above code produces the following result:
Week Start: Sep 20 2004 12:00AM -- Which is MondayWeek End: Sep 26 2004 12:00AM -- Which is Sunday 
