Solved

# How to populate the Week Number inside the Date Dimension?

Posted on 2010-11-29
961 Views
Hello,

Does anyone have a script or a guide to load the week number (1-53) column in a date dimension
The week starts on Sunday and ends on Saturday.

Thank you
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2

LVL 14

Expert Comment

ID: 34234272
SELECT DATEPART(week, @thedate)
0

LVL 1

Expert Comment

ID: 34236668
SELECT DATEPART(WEEK, GETDATE())
OR
SELECT DATEPART(wk, GETDATE())
OR
SELECT DATEPART(ww, GETDATE())
0

Author Comment

ID: 34238757
The problem is the results of the above queries is different than this http://2010-calendar.org/
The query is producing W+1 compared to the calendar in the link

Example: For day 2010-01-01:
The query is producing Week 1, while the link denotes Week 53 of the previous year

0

LVL 14

Expert Comment

ID: 34238814

SELECT case DATEPART(WEEK, GETDATE())
when 1 then 53
else DATEPART(WEEK, GETDATE()) + 1
end
0

LVL 14

Expert Comment

ID: 34238819
Actually you'd want this...

SELECT case DATEPART(WEEK, GETDATE())
when 1 then 53
else DATEPART(WEEK, GETDATE()) - 1
end
0

Author Comment

ID: 34238992
Unfortunately, it didn't work
Applying your suggested query worked unless the year starts with a Sunday (example 2012)
In such case the first week of the year will be Week 53 instead of 1

I think the query to populate the dimension has to have the calendar week logic in order to cover all cases...
0

Author Comment

ID: 34239079
Is there a way to query this logic:
"ISO 8601 defines the Week as always starting with Monday. The first week is the week which contains the first Thursday of the calendar year. This implies that it is the week which is mostly within the Calendar year and the week containing January 4th"
The only difference is that the starting day should be Sunday; instead of Monday.
0

LVL 14

Assisted Solution

Christopher Gordon earned 175 total points
ID: 34239099
0

LVL 1

Accepted Solution

_DJ earned 325 total points
ID: 34247239
DECLARE @DateTime DATETIME
SELECT @DateTime = GETDATE()

SELECT CASE DATEPART(WEEK, DATEADD(yy, DATEDIFF(yy, 0, @DateTime), 3))
WHEN 1 THEN DATEPART(WEEK, @DateTime)
ELSE CASE WHEN DATEPART(WEEK, @DateTime) = 1 THEN 53 ELSE DATEPART(WEEK, @DateTime) - 1 END END
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carriesâ€¦
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
Nobody understands Phishing better than an anti-spam company. Thatâ€™s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With companâ€¦
Introduction to Processes
###### Suggested Courses
Course of the Month3 days, 6 hours left to enroll