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 
See also  How College Students Can Shape the Future of Tech Responsibility
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist