SQL SERVER – UDF to Return a Calendar for Any Date for Any Year

It gives me great pleasure to write articles like today’s one because I have received great comment from one of regular reader who has taken UDF written by me and created another UDF using that UDF which enhances functionality of it. I had written previous article about SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar. Reader of this blog and great SQL expert Dan Golden has wrote another UDF which uses UDF written by me. I thank Dan Golden for his contribution to this blog. I have modified his function a bit to remove error when it was missing displaying first or last week in few cases, however I will give full credit to him for his thought.

I also encourage my other readers to submit their articles to me, if I think your article will benefit other readers I will publish it here with your name. Do not send me article if it is not written by you.

Step 1 : Create UDF to display current week date and day – Weekly Calender.
USE AdventureWorks;
GO
CREATE FUNCTION dbo.DisplayCurrentWeekDays
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
/*
Purpose: To return the weekly calendar for the week any date
By: Pinal Dave -- SQLAuthority.com
On: 6/8/2007
*/
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END
GO

Step 2 : Create UDF to display current month date and day – Monthly Calender
USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetSQLcalendar(@WhatDate SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
/*
Purpose: To return a calendar fOR whatever MONTH you want,
driven by the date you pick AS a variable.
By: Dan Golden
On: 2/20/2008
*/
DECLARE @FourWeeksAgo SMALLDATETIME
SET @FourWeeksAgo = DATEADD(d,-28,@WhatDate)
DECLARE @ThreeWeeksAgo SMALLDATETIME
SET @ThreeWeeksAgo = DATEADD(d,-21,@WhatDate)
DECLARE @TwoWeeksAgo SMALLDATETIME
SET @TwoWeeksAgo = DATEADD(d,-14,@WhatDate)
DECLARE @PreviousWeek SMALLDATETIME
SET @PreviousWeek = DATEADD(d,-7,@WhatDate)
DECLARE @ThisWeek SMALLDATETIME
SET @ThisWeek = (@WhatDate)
DECLARE @NextWeek SMALLDATETIME
SET @NextWeek = DATEADD(d,7,@WhatDate)
DECLARE @LastWeek SMALLDATETIME
SET @LastWeek = DATEADD(d,14,@WhatDate)
DECLARE @AfterLastWeek SMALLDATETIME
SET @AfterLastWeek = DATEADD(d,21,@WhatDate)
DECLARE @TwoAfterLastWeek SMALLDATETIME
SET @TwoAfterLastWeek = DATEADD(d,28,@WhatDate)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday,
Wednesday, Thursday, Friday, Saturday)
SELECT *
FROM
(
SELECT CASE MONTH(calendar.Sunday)
WHEN MONTH(@WhatDate)
THEN calendar.Sunday
ELSE NULL END AS 'Sunday',
CASE MONTH(calendar.Monday)
WHEN MONTH(@WhatDate)
THEN calendar.Monday
ELSE NULL END AS 'Monday',
CASE MONTH(calendar.Tuesday)
WHEN MONTH(@WhatDate)
THEN calendar.Tuesday
ELSE NULL END AS 'Tuesday',
CASE MONTH(calendar.Wednesday)
WHEN MONTH(@WhatDate)
THEN calendar.Wednesday
ELSE NULL END AS 'Wednesday',
CASE MONTH(calendar.Thursday)
WHEN MONTH(@WhatDate)
THEN calendar.Thursday
ELSE NULL END AS 'Thursday',
CASE MONTH(calendar.Friday)
WHEN MONTH(@WhatDate)
THEN calendar.Friday
ELSE NULL END AS 'Friday',
CASE MONTH(calendar.Saturday)
WHEN MONTH(@WhatDate)
THEN calendar.Saturday
ELSE NULL END AS 'Saturday'
FROM
(
SELECT *
FROM dbo.DisplayCurrentWeekDays(@FourWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThreeWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@PreviousWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThisWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@NextWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@LastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@AfterLastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoAfterLastWeek)
) calendar
) dates
WHERE dates.sunday IS NOT NULL
OR dates.monday IS NOT NULL
OR dates.tuesday IS NOT NULL
OR dates.wednesday IS NOT NULL
OR dates.thursday IS NOT NULL
OR dates.friday IS NOT NULL
OR dates.saturday IS NOT NULL
RETURN
END
GO

Step 3 : Use recently created UDF to get the current month ‘s calender. You can pass any date to this calender and it will return that month and years calender.
USE AdventureWorks;
GO
DECLARE @WhatDay SMALLDATETIME
SELECT @WhatDay = GETDATE(); -- you can also put here any date like 07/01/1990
SELECT ISNULL(CONVERT(NVARCHAR(20), Sunday, 101), '') AS 'Sunday',
ISNULL(CONVERT(NVARCHAR(20), Monday, 101), '') AS 'Monday',
ISNULL(CONVERT(NVARCHAR(20), Tuesday, 101), '') AS 'Tuesday',
ISNULL(CONVERT(NVARCHAR(20), Wednesday, 101), '') AS 'Wednesday',
ISNULL(CONVERT(NVARCHAR(20), Thursday, 101), '') AS 'Thursday',
ISNULL(CONVERT(NVARCHAR(20), Friday, 101), '') AS 'Friday',
ISNULL(CONVERT(NVARCHAR(20), Saturday, 101), '') AS 'Saturday'
FROM dbo.GetSQLcalendar(@WhatDay)
GO

Above query will return you monthly calender for date passed to function.

Reference : Pinal Dave (https://blog.sqlauthority.com) , Dan Golden