Handling Weeks Correctly in SQL

[Nassy]

Hi all,

I have written a routine that creates a calendar that contains a list of dates (dating back from 01/01/2000) and that assigns each date a Week Number, Month Name, Quarter and Year.

The Issue I am having

I simply use the function DatePart ie DatePart(wk,Table.Day) to assign a Week Number to each day. I use Sunday as my Starting Week Date.

This works fine for most weeks however....

SQL always seems to force the first of the Month of each Year eg 01/01/2003 to be Week Number 1 even though this is not always a Sunday. Meaning the last week in the year will usually have less than 7 days and first week of the new year will also be shorter than normal.

The problem is that I need my calendar to allow the final week of the year to span more than one year.

To illustrate this 2003-12-28 is the last Sunday in the year of 2003 and I would like SQL to represent the following dates as:



2003-12-28 week 53
2005-12-29 week 53
2005-12-30 week 53
2005-12-31 week 53
2006-01-01 week 53
2006-01-02 week 53
2006-01-03 week 53
2006-01-04 week 1


and not as


2003-12-28 week 53
2005-12-29 week 53
2005-12-30 week 53
2005-12-31 week 53
2006-01-01 week 1
2006-01-02 week 1
2006-01-03 week 1
2006-01-04 week 2


I am not really sure how to sort this out without writing an algorithm to do it (which I imagine might get messy). I was wondering if anyone might know if there is away to get round this problem.

cheers

Nassy

 

[gmmastros]

Take a look at this article. It may help.

http://www.sqlservercentral.com/columnists/chedgate/onthetrailoftheisoweek.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Also....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

Scroll down to the first example.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Nassy]

Thanks - these articles are what I'm looking for. I will have a read through them.

cheers

Nassy

 

[Nassy]

Hi everyone,

Thanks for the responses. Luckily the way the client wanted me to specify a week made this very straightforward.
They always wanted week 1 to start from the last Sunday of the previous year unless the new year happens to start on a Sunday.

Therefore it was a simple case of using the SQL DatePart function and then just patching up the results by setting the week number to 1 if the date was between the last Sunday of the previous year and the start of the new year. This gives me a continuous set of weeks with 7 days.



Update Dim_Time
Set WeekNumber = 1
where
Date >= (cast('01/01/'+ Cast((Year(Date)+1) as nvarchar(5))as datetime)- (DatePart(dw,cast('01/01/'+ Cast((Year(Date)+1) as nvarchar(5))as datetime))-1))
and
Date <= cast('01/01/'+ Cast((Year(Date)+1) as nvarchar(5))as datetime)



Nassy