SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar

In analytics section of our product I frequently have to display the current week dates with days. Week starts from Sunday. We display the data considering days as column and date and other values in column. If today is Friday June 8, 2007. We need script which can provides days and dates for current week. Following script will generate the required script.

DECLARE @day INT
DECLARE @today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
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

Above script can be converted in function.



CREATE FUNCTION dbo.udf_DisplayCurrentWeekDateDays
(@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) 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

The function can be execute for any day to return the week information for the week the day belongs.

----Following will return previous week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,-7,GETDATE()))
GO

----Following will return current week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(GETDATE())
GO

----Following will return next week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,7,GETDATE()))
GO



Result Set: (Today)
Sunday Monday Tuesday
———————– ———————– ———————–
2007-08-05 00:00:00.000 2007-08-06 00:00:00.000 2007-08-07 00:00:00.000
Wednesday Thursday Friday
———————– ———————– ———————–
2007-08-08 00:00:00.000 2007-08-09 00:00:00.000 2007-08-10 00:00:00.000
Saturday
———————–
2007-08-11 00:00:00.000

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