Occasionally, when querying for report data, you need to divide the data into weeks. This function returns a table that contains the date of the end of each week that ends in the specified month.
The function consumes two other functions that use statements specified in this T-SQL Begin Date post and this T-SQL End Date post.
Returning a table from a routine such as this is usually very helpful. It can easily be used in JOIN’s and/or WHERE clauses.
CREATE FUNCTION [dbo].[ft_tfm_WeekEndDatesForMonth] (
@InputDate DATETIME
)
RETURNS @Results TABLE
( SequenceNumber INTEGER IDENTITY(0,1)
,EndDay DATETIME
)
AS
BEGIN
DECLARE @MonthBegin DATETIME
DECLARE @MonthEnd DATETIME
DECLARE @tmpDate DATETIME
SET @MonthBegin = dbo.f_tfm_FirstDayOfMonth(@InputDate)
SET @MonthEnd = dbo.f_tfm_LastDayOfMonth(@InputDate)
SET @tmpDate = @MonthBegin
WHILE (@tmpDate <= @MonthEnd)
BEGIN
IF DATEPART(dw, @tmpDate) = 3 --(= Tuesday)
BEGIN
INSERT @Results (EndDay)
VALUES (@tmpDate)
END
SET @tmpDate = DATEADD(dd, 1, @tmpDate)
END
RETURN
END
GO
@InputDate DATETIME
)
RETURNS @Results TABLE
( SequenceNumber INTEGER IDENTITY(0,1)
,EndDay DATETIME
)
AS
BEGIN
DECLARE @MonthBegin DATETIME
DECLARE @MonthEnd DATETIME
DECLARE @tmpDate DATETIME
SET @MonthBegin = dbo.f_tfm_FirstDayOfMonth(@InputDate)
SET @MonthEnd = dbo.f_tfm_LastDayOfMonth(@InputDate)
SET @tmpDate = @MonthBegin
WHILE (@tmpDate <= @MonthEnd)
BEGIN
IF DATEPART(dw, @tmpDate) = 3 --(= Tuesday)
BEGIN
INSERT @Results (EndDay)
VALUES (@tmpDate)
END
SET @tmpDate = DATEADD(dd, 1, @tmpDate)
END
RETURN
END
GO
This routine has made a number of reports much easier to write. I hope you find it useful too!
(Visited 127 times, 1 visits today)
Leave a Reply