How can I bucket a date column to weekly when this week is marked as 1 and last week is 2 and so on?

Sam332 21 Reputation points
2022-11-15T04:45:34.393+00:00

Hello,
I have a table that looks like this
UserName | DateCreated
User1 | 11/13/2022
User2 | 11/10/2022
User3 | 11/6/2022
User4 | 11/1/2022
User5 | 10/28/2022

How can I create another column that will create a weekly bucket based on the DateCreated column so it will look like this
Today's Date is 11/14

UserName | DateCreated | WeeksAgo
User1 | 11/13/2022 | 1
User2 | 11/10/2022 | 2
User3 | 11/6/2022 | 2
User4 | 11/1/2022 | 3
User5 | 10/28/2022 | 4

Each week starts at Sunday and ends on Saturday
I tried with Datepart(week, DateCreated) but its calculating the weeks from the beginning of this year so last week will be 47 which It should be 2 in my case. How can I go about this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Cooper 8,466 Reputation points
    2022-11-15T05:09:50.743+00:00

    One way 

    Declare @Sample Table(UserName varchar(20), DateCreated date);  
Insert @Sample(UserName, DateCreated) Values  
('User1', '11/13/2022'),  
('User2', '11/10/2022'),  
('User3', '11/6/2022'),  
('User4', '11/1/2022'),  
('User5', '10/28/2022');  
  
Select UserName,   
   DateCreated,  
   DateDiff(day, '18991231', GetDate())/7 - DateDiff(day, '18991231', DateCreated)/7 + 1 As WeeksAgo  
From @Sample;

    The reason that works is that Dec 31, 1899 was a Sunday. You could use any other Sunday date and get the same result.

    Tom

    0 comments
  2. CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor
    2022-11-15T06:03:09.893+00:00

    Hi @Sam332

    Each week starts at Sunday and ends on Saturday

    The first day of the week is based on your language settings of the server. The default setting for us_english is 7 (Sunday)
    You can find the current first day of the week by using SELECT @@DATEFIRST
    You could set Sunday to the first day of the week for the current connection by this SET DATEFIRST 7

    How can I create another column that will create a weekly bucket based on the DateCreated column

    You could use Datediff function, try this query: 

    Declare @Sample Table(UserName varchar(20), DateCreated date);  
Insert @Sample(UserName, DateCreated) Values  
 ('User1', '11/13/2022'),  
 ('User2', '11/10/2022'),  
 ('User3', '11/6/2022'),  
 ('User4', '11/1/2022'),  
 ('User5', '10/28/2021');  
      
Select UserName,   
       DateCreated,  
       Datediff(wk,DateCreated,Getdate()) + 1 As WeeksAgo  
From @Sample;

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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.

    0 comments