Today, we will be discussing SQL Server functions and how they can enhance the functionality of your database. Functions are a powerful tool in SQL Server that allow you to perform calculations, manipulate data, and retrieve specific information. They can be used to simplify complex queries and improve the efficiency of your database operations.
In this article, we will focus on two user-defined functions (UDFs) that demonstrate the capabilities of SQL Server functions. These UDFs were created by a reader of this blog, Dan Golden, who built upon a previously written UDF by the author. The collaboration between the reader and the author showcases the community-driven nature of SQL Server development.
Step 1: Create UDF to Display Current Week Date and Day – Weekly Calendar
The first UDF, dbo.DisplayCurrentWeekDays, is designed to return the weekly calendar for any given date. It takes a SMALLDATETIME parameter and returns a table with columns for each day of the week. The function calculates the dates for the current week based on the input date and inserts them into the table.
CREATE FUNCTION dbo.DisplayCurrentWeekDays (@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE (
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
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) AS Sunday,
DATEADD(dd, 2 - @day, @today) AS Monday,
DATEADD(dd, 3 - @day, @today) AS Tuesday,
DATEADD(dd, 4 - @day, @today) AS Wednesday,
DATEADD(dd, 5 - @day, @today) AS Thursday,
DATEADD(dd, 6 - @day, @today) AS Friday,
DATEADD(dd, 7 - @day, @today) AS Saturday
RETURN
END
Step 2: Create UDF to Display Current Month Date and Day – Monthly Calendar
The second UDF, dbo.GetSQLcalendar, builds upon the previous UDF to return a calendar for a specific month. It takes a SMALLDATETIME parameter and returns a table with columns for each day of the week. The function calculates the dates for the current month based on the input date and inserts them into the table.
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
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
Step 3: Use the Recently Created UDF to Get the Current Month’s Calendar
Now that we have our UDFs in place, we can use them to retrieve the current month’s calendar. By passing a date to the dbo.GetSQLcalendar function, we can obtain the calendar for that specific month and year. In the example below, we use the current date as the input.
DECLARE @WhatDay SMALLDATETIME
SELECT @WhatDay = GETDATE() -- You can also put any date here, such as '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)
The above query will return the monthly calendar for the date passed to the function. You can modify the input date to retrieve the calendar for any specific month and year.
By leveraging the power of SQL Server functions, you can enhance the functionality of your database and simplify complex queries. The collaboration between the author and the reader in this article demonstrates the community-driven nature of SQL Server development. Feel free to explore and experiment with these functions to further optimize your SQL Server environment.
Happy coding!