SQL Server 2008
There are some definitions for calendar weeks and T-SQL offers many of those calculations out of the box. When you’re doing projects in Europe you will frequently find the ISO week definition for the calendar week as of ISO 8601. Week 01 in the ISO week calendar is defined is week with the year’s first Thursday in it and weeks start with Monday and end with Sunday. The notation described in the ISO looks for exmaple like this 2009-W28-6. This would mean the sixth day of the ISO week (i.e. Saturday as the ISO week starts with Monday) of week 28 in the year 2009.
Fortunately T-SQL 2008 knows how to compute the ISO week number using the datapart function, for example
-
declare @dt date;
-
set @dt=CAST(‚2009-7-11‘ as DATE)
-
select DATEPART(ISO_WEEK,@dt)
results in 28 (week 28).
Now for the day of the week we can again use datepart, this time with the WEEKDAY parameter:
-
select DATEPART(WEEKDAY,@dt)
Depending on your machine settings this will most likely return 7 (as Saturday is the 7th day of the week when starting with Sunday=1). We can override the first day setting by using the following statement:
-
set datefirst 1;
After doing so the result of the above statement would be 6 as required. If you don’t want to use the datefirst setting you could as well correct the day number manually using the modulus operator %:
-
select (DATEPART(weekday,@dt)+5) % 7+1
So up till now we have our week and the day. The remaining thing is the year. You might expect this to be very easy as the T-SQL year function extracts the year from a given date. But the year of the calendar week might differ from the calendar year, for example December 29, 2008 would be in the ISO week 1 of 2009 (2009-W1-1).
Therefore we need to compute the proper week. A way to do so is like this:
-
select DATEPART(year,DATEADD(d,-(DATEPART(weekday,@dt)+5) % 7+3,@dt))
Putting it all together we end up with the following computation:
-
cast(DATEPART(year,DATEADD(d,-(DATEPART(weekday,@dt)+5) % 7+3,@dt)) as nvarchar)
-
+N‘-W‘+CAST(DATEPART(ISO_WEEK,@dt) as nvarchar)
-
+N‘-‚+cast((DATEPART(weekday,@dt)+5) % 7+1 as nvarchar)
In order to use this expression in your data source views (DSV) computation you would have to replace the @dt variable with the specific date field and you have to make sure that the datefirst parameter is set to 7 (default), i.e.
-
select @@datefirst
should return the value 7.
Kommentare (0)