Calendar Table

One of the useful user created tables that would benefit in most of the queries is Calendar table.
It is easy to populate the data in it one time only and used it as many as possible.
This is one of the most common questions that being asked in Microsoft Forums as I seen.
I am creating the Calendar table with following fields in it.


use DBTest
Go
Create Table Calendar
(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)
Go


I am assigning the permissions to this table.

Grant Select On Object::dbo.Calendar to Public Grant Insert On Object::dbo.Calendar to Public Grant Update On Object::dbo.Calendar to Public Grant Delete On Object::dbo.Calendar to Public

Go



Populate the data and insert it into Calendar table.


Declare @beginDate Date, @endDate Date
Select @beginDate = '01/01/1900', @endDate = '12/31/2030'


While @beginDate <= @endDate
Begin
    Insert Into dbo.Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)
    Select
        @beginDate As CalendarDate     
        ,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
        ,DATEPART(Year, @beginDate) As YearNo
        ,DATEPART(QUARTER, @beginDate) As QuarterNo
        ,DATEPART(MONTH, @beginDate) As MonthNo
        ,DATEPART(DayOfYear, @beginDate) As DayOfYearNo
        ,DATEPART(Day, @beginDate) As DayNo         
        ,DATEPART(Week, @beginDate) As WeekNo
        ,DATEPART(WEEKDAY, @beginDate) As WeekDayNo
    Set @beginDate = DateAdd(Day, 1, @beginDate)
End


Select CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo From dbo.Calendar


Sample of fetching data based on Year, Week Number And Week Day No and compare it simple select statement from Calendar table
Declare @myDate As Varchar(30)

Set @myDate = '2010-38-6' --(Year, WeekNo, WeekDayNo)


Declare @YearNo SmallInt, @WeekNo TinyInt, @DayNo TinyInt


Set @YearNo = Convert(SmallInt, Left(@myDate, 4))
Set @WeekNo = Convert(TinyInt, SubString(@myDate, PatIndex('%-%', @myDate) + 1, 2))
Set @DayNo = Convert(TinyInt, Right(@myDate, 1))


Select
    DateAdd(WK, @WeekNo - 1,
    DateAdd(Year, @YearNo - 1900, 0)) - 6 + @DayNo
--Or Easiest way
Select CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo
From dbo.Calendar
Where YearNo = @YearNo And WeekNo = @WeekNo And WeekDayNo = @DayNo


Checking the performance
Select
    CalendarDate
    ,IsWeekend
    ,YearNo
    ,QuarterNo
    ,MonthNo
    ,DayOfYearNo
From
    dbo.calendar
Where
    DATEPART(Year, CalendarDate) = 2011
    And DATEPART(Month, CalendarDate) = 6
    
Select
    CalendarDate
    ,IsWeekend
    ,YearNo
    ,QuarterNo
    ,MonthNo
    ,DayOfYearNo
From
    dbo.calendar
Where
    CalendarDate >= DATEADD(Month, 12 * DatePart(Year, GetDate()) - 22801 + DatePart(Month, GetDate()), 0)
    And CalendarDate < DateAdd(Month, 1, DATEADD(Month, 12 * DatePart(Year, GetDate()) - 22801 + DatePart(Month, GetDate()), 0))