I want to make a time dimension with TSQL. To get the week number I use datepart(week, @data_variable). The problem is that the function is not compatible with ISO 8601. The first day each year is always part of week number 1 and the last day is never part of week number 1.
It seems that the datpart function in VB has all this functionality:
DatePart(interval,date[,firstdayofweek[,firstweekofyear]])
but the TSQL has not. See http://www.w3schools.com/vbscript/func_datepart.asp for a good description.
Is there an easy way to create a time dimension with ISO 8601 compatible week numbering with TSQL
This is the code I use now but that give the wrong week numbering:
CREATE TABLE [DIM_Tid] (
[DIM_Tid_dKey] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [char] (4) NULL ,
[Month] [char] (2) NULL ,
[WeekNr] [char] (2) NULL ,
[Day] [char] (2) NULL ,
[Weekday] [char] (2) NULL ,
[DateTime] [datetime] NULL
) ON [PRIMARY]
declare @first smalldatetime
declare @last smalldatetime
declare @day smalldatetime
select @first = '1998-01-01'
select @last = '2007-12-31'
select @day = @first
WHILE @day < @last
BEGIN
INSERT INTO [DIM_Time]([Year], [Month], [WeekNr], [Day], [Weekday], [DateTime])
VALUES(
datepart(year ,@day),
datepart(month ,@day),
datepart(week ,@day),
datepart(day ,@day),
datepart(weekday, @day),
@day
)
select @day = @day + 1
END
Regards,
Joel
ISO 8601 complient week numbering
Naseem