How we can add week days like Monday , Tuesday so on and time with it.

Question

This query shows week by week data collection, But how we can add week days like Monday , Tuesday so on and time with it.

Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
  
CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15))  
While (@beginDate < 9 and @pendDate < 10)  
   BEGIN  
    INSERT INTO #Test  
    
          Select Datepart(wk,DATEADD(WEEK, -1 * @beginDate ,'2017-12-31')),Datepart(wk,DATEADD(WEEK, -1 * @pendDate ,'2017-12-31'))  
  
     
            SET @beginDate += 1  
 SET @pendDate +=1  
        END  
  
SELECT * FROM #Test

Accepted Answer

Hi @RXR ,

Please refer below which is mostly close to your expectation.

drop table if exists  #Test  
  
SET DATEFIRST 1 ;    
  
Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
      
 CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
 While (@beginDate < 7 and @pendDate < 8)  
    BEGIN  
     INSERT INTO #Test  
        
     Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
	 Datepart(wk,DATEADD(day, -1 * @pendDate-6 ,'2017-12-31')),  
	 DATENAME(DW,Dateadd(day,1 *@beginDate,'2017-12-31'));  
     
  SET @beginDate += 1  
  SET @pendDate +=1  
END  
      
 SELECT * FROM #Test

Output:
WeekStart WeekEnd WeekDay
53 52 Sunday
53 52 Monday
53 52 Tuesday
53 52 Wednesday
53 52 Thursday
53 52 Friday
53 52 Saturday

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Hi @RXR ,

Thanks for posting here.

The output of your query is different from your desired output.

WeekStart WeekEnd
53 52
52 51
51 50
50 49
49 48
48 47
47 46
46 45
45 44

Besides, what is the first day of one week in your requirement? Monday ,Sunday or other day?

You could use below command to set the first day as you like .

SET DATEFIRST { number | @number_var }     
  
--For example   
SET DATEFIRST 7; --Sunday is the first day of one week  
  
SET DATEFIRST 3; --Wednesday is now considered the first day of the week

I updated your query and added one WeekDay column. I used the default first day of one week as Sunday.

Please refer below and check whether it is helpful to you.

drop table if exists  #Test  
  
Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
      
 CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
 While (@beginDate < 9 and @pendDate < 10)  
    BEGIN  
     INSERT INTO #Test  
        
     Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
  Datepart(wk,DATEADD(day, -1 * @pendDate ,'2017-12-31')),  
  DATENAME(DW,Dateadd(day,1*@beginDate,'2017-12-31'));  
     
  SET @beginDate += 1  
  SET @pendDate +=1  
END  
      
 SELECT * FROM #Test

Output:
WeekStart WeekEnd WeekDay
53 52 Sunday
52 52 Monday
52 52 Tuesday
52 52 Wednesday
52 52 Thursday
52 52 Friday
52 52 Saturday
52 51 Sunday
51 51 Monday

After checking, actually '2017-12-30' should be Saturday instead of Monday.

Please also refer below:

drop table if exists  #Test  
  
Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
      
 CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
 While (@beginDate < 9 and @pendDate < 10)  
    BEGIN  
     INSERT INTO #Test  
        
     Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
  Datepart(wk,DATEADD(day, -1 * @pendDate ,'2017-12-31')),  
  DATENAME(DW,Dateadd(day,-1 *@beginDate,'2017-12-31'));  
     
  SET @beginDate += 1  
  SET @pendDate +=1  
END  
      
 SELECT * FROM #Test

Output:
WeekStart WeekEnd WeekDay
53 52 Sunday
52 52 Saturday
52 52 Friday
52 52 Thursday
52 52 Wednesday
52 52 Tuesday
52 52 Monday
52 51 Sunday
51 51 Saturday

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

You should use a calendar table:

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Answer

I can't say that I find your question crystal clear, but maybe this is what you are looking for:

     SELECT * FROM #Test
     CROSS JOIN (VALUES('Sunday'), ('Monday'), ('Tuesday'), ('Wednesday'), 
                       ('Thursday'), ('Friday'), ('Saturday'), ('Sunday')) AS V(Days)

Share via

How we can add week days like Monday , Tuesday so on and time with it.

3 additional answers

Your answer