How to design a Calender table in Sql Server 2008 R2

Hi create one table and update the table as your holidays for example table below script:

--To create table

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[DIM_Date](

[DateKey] [int] NOT NULL,

[DateValue] [date] NULL,

[FullDate_DDMMYYYY] [char](10) NULL,

[FullDate_MMDDYYYY] [char](10) NULL,

[FullDate_DDMMMYYYY] [char](11) NULL,

[DayOfMonth] [char](10) NULL,

[DayOfWeekFull] [char](10) NULL,

[DayOfWeekShort] [char](10) NULL,

[Month] [char](10) NULL,

[MonthNameFull] [char](10) NULL,

[MonthNameShort] [char](10) NULL,

[Quarter] [char](1) NULL,

[QuarterName] [char](10) NULL,

[Year] [char](4) NULL,

[YearName] [char](7) NULL,

[MonthYear] [char](10) NULL,

[FirstDayOfMonth] [date] NULL,

[LastDayOfMonth] [date] NULL,

[FirstDayOfQuarter] [date] NULL,

[LastDayOfQuarter] [date] NULL,

[FirstDayOfYear] [date] NULL,

[LastDayOfYear] [date] NULL,

[IsWeekday] [bit] NULL,

[IsHoliday] [bit] NULL,

CONSTRAINT [PK_Dim_Date] PRIMARY KEY CLUSTERED

(

[DateKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

--and use the below procdeure to populate the dates

IF((SELECT COUNT(1) FROM sysobjects WHERE name='p_PopulateDateDimensionTable' )>0)

BEGIN

DROP PROCEDURE p_PopulateDateDimensionTable

END

SELECT * FROM [dbo].[Dim_Date]

DELETE FROM [dbo].[Dim_Date]

EXECUTE [dbo].[p_PopulateDateDimensionTable] '2004-01-01', '2018-12-31'

create PROCEDURE [dbo].[p_PopulateDateDimensionTable] (

@StartDate DATE

,@EndDate DATE

)

BEGIN

SET NOCOUNT ON;

DECLARE @Date DATE;

SET @Date = @StartDate;

WHILE (@Date <= @EndDate)

BEGIN

IF (

SELECT COUNT(*)

FROM DIM_Date

WHERE DateValue = @Date

) = 0

BEGIN

INSERT INTO [dbo].[DIM_Date] (

[DateKey]

,[DateValue]

,[FullDate_DDMMYYYY]

,[FullDate_MMDDYYYY]

,[FullDate_DDMMMYYYY]

,[DayOfMonth]

,[DayOfWeekFull]

,[DayOfWeekShort]

,[Month]

,[MonthNameFull]

,[MonthNameShort]

,[Quarter]

,[QuarterName]

,[Year]

,[YearName]

,[MonthYear]

,[FirstDayOfMonth]

,[LastDayOfMonth]

,[FirstDayOfQuarter]

,[LastDayOfQuarter]

,[FirstDayOfYear]

,[LastDayOfYear]

,[IsWeekday]

,[IsHoliday]

)

SELECT [DateKey]

,[Date]

,[FullDate_DDMMYYYY]

,[FullDate_MMDDYYYY]

,[FullDate_DDMMMYYYY]

,[DayOfMonth]

,[DayOfWeekFull]

,[DayOfWeekShort]

,[Month]

,[MonthNameFull]

,[MonthNameShort]

,[Quarter]

,[QuarterName]

,[Year]

,[YearName]

,[MonthYear]

,[FirstDayOfMonth]

,[LastDayOfMonth]

,[FirstDayOfQuarter]

,[LastDayOfQuarter]

,[FirstDayOfYear]

,[LastDayOfYear]

,[IsWeekday]

,0 AS [IsHoliday]

FROM [dbo].[fn_GetDateDimensionAttributes](@Date);

SET @Date = DATEADD(DAY, 1, @Date);

END;

ELSE

BEGIN

SET @Date = DATEADD(DAY, 1, @Date);

END;

SET NOCOUNT OFF;

END;

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed9y ago
Hi create one table and update the table as your holidays for example table below script:
--To create table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DIM_Date](
[DateKey] [int] NOT NULL,
[DateValue] [date] NULL,
[FullDate_DDMMYYYY] [char](10) NULL,
[FullDate_MMDDYYYY] [char](10) NULL,
[FullDate_DDMMMYYYY] [char](11) NULL,
[DayOfMonth] [char](10) NULL,
[DayOfWeekFull] [char](10) NULL,
[DayOfWeekShort] [char](10) NULL,
[Month] [char](10) NULL,
[MonthNameFull] [char](10) NULL,
[MonthNameShort] [char](10) NULL,
[Quarter] [char](1) NULL,
[QuarterName] [char](10) NULL,
[Year] [char](4) NULL,
[YearName] [char](7) NULL,
[MonthYear] [char](10) NULL,
[FirstDayOfMonth] [date] NULL,
[LastDayOfMonth] [date] NULL,
[FirstDayOfQuarter] [date] NULL,
[LastDayOfQuarter] [date] NULL,
[FirstDayOfYear] [date] NULL,
[LastDayOfYear] [date] NULL,
[IsWeekday] [bit] NULL,
[IsHoliday] [bit] NULL,
CONSTRAINT [PK_Dim_Date] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--and use the below procdeure to populate the dates
IF((SELECT COUNT(1) FROM sysobjects WHERE name='p_PopulateDateDimensionTable' )>0)
BEGIN
DROP PROCEDURE p_PopulateDateDimensionTable
END
Go
/*
SELECT * FROM [dbo].[Dim_Date]
DELETE FROM [dbo].[Dim_Date]
EXECUTE [dbo].[p_PopulateDateDimensionTable] '2004-01-01', '2018-12-31'
*/
create PROCEDURE [dbo].[p_PopulateDateDimensionTable] (
@StartDate DATE
,@EndDate DATE
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Date DATE;
SET @Date = @StartDate;
WHILE (@Date <= @EndDate)
BEGIN
IF (
SELECT COUNT(*)
FROM DIM_Date
WHERE DateValue = @Date
) = 0
BEGIN
INSERT INTO [dbo].[DIM_Date] (
[DateKey]
,[DateValue]
,[FullDate_DDMMYYYY]
,[FullDate_MMDDYYYY]
,[FullDate_DDMMMYYYY]
,[DayOfMonth]
,[DayOfWeekFull]
,[DayOfWeekShort]
,[Month]
,[MonthNameFull]
,[MonthNameShort]
,[Quarter]
,[QuarterName]
,[Year]
,[YearName]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[IsWeekday]
,[IsHoliday]
)
SELECT [DateKey]
,[Date]
,[FullDate_DDMMYYYY]
,[FullDate_MMDDYYYY]
,[FullDate_DDMMMYYYY]
,[DayOfMonth]
,[DayOfWeekFull]
,[DayOfWeekShort]
,[Month]
,[MonthNameFull]
,[MonthNameShort]
,[Quarter]
,[QuarterName]
,[Year]
,[YearName]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[IsWeekday]
,0 AS [IsHoliday]
FROM [dbo].[fn_GetDateDimensionAttributes](@Date);
SET @Date = DATEADD(DAY, 1, @Date);
END;
ELSE
BEGIN
SET @Date = DATEADD(DAY, 1, @Date);
END;
END;
SET NOCOUNT OFF;
END;