Table Function For Data Warehouse Time Dimension

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER FUNCTION fn_TimeDimension(@StartDate datetime, @EndDate datetime)
RETURNS @TimeDimension TABLE
(
[TimeKey] integer NOT NULL,
[TheDate] datetime NOT NULL,
[ISODate] char(8) NOT NULL,

[WeekName] varchar(7) NOT NULL,
[WeekNameWithYear] varchar(13) NOT NULL,
[WeekShortName] char(4) NOT NULL,
[WeekShortNameWithYear] char(9) NOT NULL,
[WeekNumber] tinyint NOT NULL,
[FirstDateOfWeek] datetime NOT NULL,
[LastDateOfWeek] datetime NOT NULL,
[DayOfWeek] tinyint NOT NULL,
[DayOfWeekName] varchar(9) NOT NULL,
[IsWeekday] bit NOT NULL,
[IsWeekend] bit NOT NULL,

[MonthName] varchar(9) NOT NULL,
[MonthNameWithYear] varchar(15) NOT NULL,
[MonthShortName] varchar(3) NOT NULL,
[MonthShortNameWithYear] char(8) NOT NULL,
[MonthNumber] tinyint NOT NULL,
[FirstDateOfMonth] datetime NOT NULL,
[LastDateOfMonth] datetime NOT NULL,
[DayOfMonth] tinyint NOT NULL,
[DayOfMonthName] varchar(16) NOT NULL,

[QuarterName] char(9) NOT NULL,
[QuarterNameWithYear] char(15) NOT NULL,
[QuarterShortName] char(2) NOT NULL,
[QuarterShortNameWithYear] char(7) NOT NULL,
[QuarterNumber] tinyint NOT NULL,
[FirstDateOfQuarter] datetime NOT NULL,
[LastDateOfQuarter] datetime NOT NULL,
[DayOfQuarter] tinyint NOT NULL,
[DayOfQuarterName] varchar(16) NOT NULL,

[HalfName] char(6) NOT NULL,
[HalfNameWithYear] char(12) NOT NULL,
[HalfShortName] char(2) NOT NULL,
[HalfShortNameWithYear] char(7) NOT NULL,
[HalfNumber] tinyint NOT NULL,
[FirstDateOfHalf] datetime NOT NULL,
[LastDateOfHalf] datetime NOT NULL,
[DayOfHalf] tinyint NOT NULL,
[DayOfHalfName] varchar(16) NOT NULL,

[YearName] char(4) NOT NULL,
[YearShortName] char(2) NOT NULL,
[YearNumber] smallint NOT NULL,
[FirstDateOfYear] datetime NOT NULL,
[LastDateOfYear] datetime NOT NULL,
[DayOfYear] smallint NOT NULL,
[DayOfYearName] varchar(20) NOT NULL
)
AS
BEGIN

DECLARE @TimeKey Int
DECLARE @TheDate DateTime
DECLARE @ISODate char(8)

DECLARE@WeekName varchar(7)
DECLARE@WeekNameWithYear varchar(13)
DECLARE@WeekShortName char(4)
DECLARE@WeekShortNameWithYear char(9)
DECLARE@WeekNumber tinyint
DECLARE @FirstDateOfWeek as datetime
DECLARE @LastDateOfWeek as datetime
DECLARE@DayOfWeek tinyint
DECLARE@DayOfWeekName varchar(9)
DECLARE @IsWeekday bit
DECLARE @IsWeekend bit

DECLARE@MonthName varchar(9)
DECLARE@MonthNameWithYear varchar(15)
DECLARE@MonthShortName varchar(3)
DECLARE@MonthShortNameWithYear char(8)
DECLARE@MonthNumber tinyint
DECLARE @FirstDateOfMonth as datetime
DECLARE @LastDateOfMonth as datetime
DECLARE @DayOfMonth tinyint
DECLARE @DayOfMonthName varchar(16)

DECLARE@QuarterName char(9)
DECLARE@QuarterNameWithYear char(15)
DECLARE@QuarterShortName char(2)
DECLARE@QuarterShortNameWithYear char(7)
DECLARE@QuarterNumber tinyint
DECLARE @FirstDateOfQuarter as datetime
DECLARE @LastDateOfQuarter as datetime
DECLARE @DayOfQuarter tinyint
DECLARE @DayOfQuarterName varchar(20)

DECLARE@HalfName char(6)
DECLARE@HalfNameWithYear char(12)
DECLARE@HalfShortName char(2)
DECLARE@HalfShortNameWithYear char(7)
DECLARE@HalfNumber tinyint
DECLARE @FirstDateOfHalf as datetime
DECLARE @LastDateOfHalf as datetime
DECLARE @DayOfHalf tinyint
DECLARE @DayOfHalfName varchar(20)

DECLARE@YearName char(4)
DECLARE@YearShortName char(2)
DECLARE@YearNumber smallint
DECLARE @FirstDateOfYear as datetime
DECLARE @LastDateOfYear as datetime
DECLARE @DayOfYear smallint
DECLARE @DayOfYearName varchar(20)


SET @TheDate = @StartDate

WHILE DateDiff(day,@TheDate,@EndDate) >= 0
BEGIN

SET @TimeKey = DateDiff(day,@StartDate,@EndDate) - DateDiff(day,@TheDate,@EndDate) + 1
SET @ISODate = convert(char(8),@TheDate,112)

SET @YearNumber = DATEPART(yy,@TheDate)
SET @YearName = right('0000' + cast(@YearNumber as varchar(4)),4)
SET @YearShortName = right('0000' + cast(@YearNumber as varchar(4)),2)
SET @FirstDateOfYear = cast(@YearName + '-01-01' as datetime)
SET @LastDateOfYear = cast(@YearName + '-12-31' as datetime)
SET @DayOfYear = DATEPART(dy, @TheDate)

SET @QuarterNumber = DATEPART(q, @TheDate)
SET @QuarterName = 'Quarter ' + cast(@QuarterNumber as char(1))
SET @QuarterNameWithYear = @QuarterName + ', ' + @YearName
SET @QuarterShortName = 'Q' + cast(@QuarterNumber as char(1))
SET @QuarterShortNameWithYear = @QuarterShortName + ' ' + @YearName
SET @FirstDateOfQuarter = 
CASE @QuarterNumber
WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
WHEN 2 THEN cast(@YearName + '-04-01' as datetime)
WHEN 3 THEN cast(@YearName + '-07-01' as datetime)
WHEN 4 THEN cast(@YearName + '-10-01' as datetime)
END
SET @LastDateOfQuarter = dateadd(day,-1,dateadd(q,1,@FirstDateOfQuarter))
SET @DayOfQuarter = DateDiff(day,@FirstDateOfQuarter, @TheDate) + 1
SET @DayOfQuarterName = 'Day ' + cast(DateDiff(day,@FirstDateOfQuarter, @TheDate) + 1 as varchar(2)) + ' of Q' + cast(@QuarterNumber as char(1))

SET @HalfNumber = CASE WHEN DATEPART(q, @TheDate) <= 2 THEN 1 ELSE 2 END
SET @HalfName = 'Half ' + cast(@HalfNumber as char(1))
SET @HalfNameWithYear = @HalfName + ', ' + @YearName
SET @HalfShortName = 'H' + cast(@HalfNumber as char(1))
SET @HalfShortNameWithYear = @HalfShortName + ' ' + @YearName
SET @FirstDateOfHalf = 
CASE @HalfNumber
WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
WHEN 2 THEN cast(@YearName + '-07-01' as datetime)
END
SET @LastDateOfHalf = 
CASE @HalfNumber
WHEN 1 THEN cast(@YearName + '-06-30' as datetime)
WHEN 2 THEN cast(@YearName + '-12-31' as datetime)
END
SET @DayOfHalf = DateDiff(day,@FirstDateOfHalf, @TheDate) + 1
SET @DayOfHalfName = 'Day ' + cast(DateDiff(day,@FirstDateOfHalf, @TheDate) + 1 as varchar(3)) + ' of H' + cast(@HalfNumber as char(1))

SET @MonthName = DATENAME(mm, @TheDate)
SET @MonthNameWithYear = @MonthName + ', ' + @YearName
SET @MonthShortName = DATENAME(m, @TheDate)
SET @MonthShortNameWithYear = @MonthShortName + ' ' + @YearName
SET @MonthNumber = DATEPART(m, @TheDate)
SET @FirstDateOfMonth = cast(@YearName + '-'+@MonthShortName+'-01' as datetime)
SET @LastDateOfMonth = dateadd(day,-1,dateadd(m,1,@FirstDateOfMonth))
SET @DayOfMonth = DATEPART(d, @TheDate)
SET @DayOfMonthName = datename(m,@TheDate) + ' ' + 
cast(datepart(d,@TheDate) as varchar(2)) +
CASE left(right('00' + cast(datepart(d,@TheDate) as varchar(2)),2),1)
WHEN '1' THEN
'th'
ELSE 
CASE right(right('00' + cast(datepart(d,@TheDate) as varchar(2)),2),1)
WHEN '1' THEN 'st'
WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd'
ELSE 'th'
END
END
SET @DayOfYearName = @DayOfMonthName + ', ' + @YearName

SET @WeekName = 'Week ' + datename(wk,@TheDate)
SET @WeekNameWithYear =@WeekName + ', ' + @YearName
SET @WeekShortName = 'WK'+right('00'+datename(wk,@TheDate),2)
SET @WeekShortNameWithYear = @WeekShortName + ' ' + @YearName
SET @WeekNumber = datepart(wk,@TheDate)

SET @FirstDateOfWeek = dateadd(day,(datepart(dw,@TheDate)-1)*-1,@TheDate)
SET @LastDateOfWeek = dateadd(day,-1,dateadd(wk,1,@FirstDateOfWeek))
SET @DayOfWeek = DATEPART(dw, @TheDate)
SET @DayOfWeekName = DATENAME(dw, @TheDate)
SET @IsWeekday = CASE @DayOfWeek WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END
SET @IsWeekend = CASE @DayOfWeek WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END

INSERT INTO @TimeDimension
(
[TimeKey],
[TheDate],
[ISODate],

[WeekName],
[WeekNameWithYear],
[WeekShortName],
[WeekShortNameWithYear],
[WeekNumber],
[FirstDateOfWeek],
[LastDateOfWeek],
[DayOfWeek],
[DayOfWeekName],
[IsWeekday],
[IsWeekend],

[MonthName],
[MonthNameWithYear],
[MonthShortName],
[MonthShortNameWithYear],
[MonthNumber],
[FirstDateOfMonth],
[LastDateOfMonth],
[DayOfMonth],
[DayOfMonthName],

[QuarterName],
[QuarterNameWithYear],
[QuarterShortName],
[QuarterShortNameWithYear],
[QuarterNumber],
[FirstDateOfQuarter],
[LastDateOfQuarter],
[DayOfQuarter],
[DayOfQuarterName],

[HalfName],
[HalfNameWithYear],
[HalfShortName],
[HalfShortNameWithYear],
[HalfNumber],
[FirstDateOfHalf],
[LastDateOfHalf],
[DayOfHalf],
[DayOfHalfName],

[YearName],
[YearShortName],
[YearNumber],
[FirstDateOfYear],
[LastDateOfYear],
[DayOfYear],
[DayOfYearName]
)
VALUES
(
@TimeKey,
@TheDate,
@ISODate,

@WeekName,
@WeekNameWithYear,
@WeekShortName,
@WeekShortNameWithYear,
@WeekNumber,
@FirstDateOfWeek,
@LastDateOfWeek,
@DayOfWeek,
@DayOfWeekName,
@IsWeekday,
@IsWeekend,


@MonthName,
@MonthNameWithYear,
@MonthShortName,
@MonthShortNameWithYear,
@MonthNumber,
@FirstDateOfMonth,
@LastDateOfMonth,
@DayOfMonth,
@DayOfMonthName,

@QuarterName,
@QuarterNameWithYear,
@QuarterShortName,
@QuarterShortNameWithYear,
@QuarterNumber,
@FirstDateOfQuarter,
@LastDateOfQuarter,
@DayOfQuarter,
@DayOfQuarterName,

@HalfName,
@HalfNameWithYear,
@HalfShortName,
@HalfShortNameWithYear,
@HalfNumber,
@FirstDateOfHalf,
@LastDateOfHalf,
@DayOfHalf,
@DayOfHalfName,

@YearName,
@YearShortName,
@YearNumber,
@FirstDateOfYear,
@LastDateOfYear,
@DayOfYear,
@DayOfYearName
)

SET @TheDate = DATEADD(day, 1, @TheDate)
END
RETURN
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/*
--------------------------------------------------------
-- Make Use Of The Table Function Like This
--------------------------------------------------------
select *
from fn_TimeDimension('01/01/1999', '01/01/2004')
*/