devxlogo

Getting the Week Start Date and Week End Date in SQL

Getting the Week Start Date and Week End Date in SQL

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 

devx-admin

Share the Post: