Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Oct 19, 2004

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 DATE
SELECT  @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW,  @REPORT_DATE) - 1) 

--GET THE WEEK END DATE
SELECT  @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 Monday
Week End: Sep 26 2004 12:00AM    -- Which is Sunday 
Raj Chidipudi
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap