Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to populate the Week Number inside the Date Dimension?

Posted on 2010-11-29
9
Medium Priority
?
1,006 Views
Last Modified: 2012-08-13
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
Comment
Question by:DBADS
[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
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34234272
SELECT DATEPART(week, @thedate)
0
 
LVL 1

Expert Comment

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

Author Comment

by:DBADS
ID: 34238757
Thanks guys for the reply
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

Please advise. This is urgent
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34238814
What about something like this...

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

Expert Comment

by:Christopher Gordon
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

by:DBADS
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

by:DBADS
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

by:Christopher Gordon
Christopher Gordon earned 700 total points
ID: 34239099
0
 
LVL 1

Accepted Solution

by:
_DJ earned 1300 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question