Sql Server: DateRanges() Function

This user-defined function creates a table of date ranges.

--================================================================================
--Author: Craig Buchanan;craig.buchanan@cogniza.com
--Purpose: Generates a table of data ranges (the most recent 12 months, plus
-- three of Crystal Reports named-date ranges: MonthToDate,LastFullMonth
-- and YearToDate
--Parameters: None
--Returns: a table of date range values
--================================================================================
DROP FUNCTION dbo.DateRanges
GO
CREATE FUNCTION dbo.DateRanges ()
RETURNS @DateRangeTable TABLE (
[Sequence] decimal,
StartDate smalldatetime,
EndDate smalldatetime,
Value varchar(25),
[Description] varchar(20)
)
AS
BEGIN
--grab the system's date/time (using a view)
DECLARE @EndDate Datetime
SELECT @EndDate = Now
FROM dbo.GetNow
--calculate the first date of the current month
DECLARE @FirstOfMonth Datetime
SET @FirstOfMonth = CONVERT(VARCHAR,DatePart(m,@EndDate)) + '/1/' + CONVERT(CHAR(4),DatePart(yyyy,@EndDate))
--calculate the first day of the month, 12 months ago
DECLARE @StartDate Datetime
SET @StartDate = DateAdd(m,-12,@FirstOfMonth)
--create a sequence number generator
DECLARE @i int
SET @i = 1
--insert 12 months of data
WHILE @StartDate < @FirstOfMonth
BEGIN
INSERT INTO @DateRangeTable (
[Sequence],
StartDate,
EndDate,
Value,
[Description]
)
VALUES (
@i,
@StartDate,
DateAdd(m,1,@StartDate)-1,
--create a value in the format i;StartDate:EndDate
--ensure i is 0 padded
Right( '0' + convert(varchar, @i), 2) + ';' +
CONVERT(CHAR(10),@StartDate,110) + ':' +
CONVERT(CHAR(10),DateAdd(m,1,@StartDate)-1,110),
DATENAME(mm, @StartDate) + ' ' + CONVERT(VARCHAR,DATEPART(yyyy, @StartDate))
)
--add a month; increment counter
SET @StartDate = DateAdd(m,1,@StartDate)
SET @i = @i + 1
END
--add crystal report data ranges in the format index;DateRange
--'Year to Date'
INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
VALUES (
@i,
'1/1/' + CONVERT(CHAR(4),DatePart(yyyy, @EndDate)),
CONVERT(CHAR(10),@EndDate,110),
Right( '0' + convert(varchar, @i), 2) + ';YearToDate','Year To Date'
)
SET @i = @i + 1
--'Last Full Month'
INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
VALUES (
@i,
CONVERT(CHAR(10),DateAdd(m,-1,@FirstOfMonth),110),
CONVERT(CHAR(10),@FirstOfMonth-1,110),
Right( '0' + convert(varchar, @i), 2) + ';LastFullMonth','Last Full Month'
)
SET @i = @i + 1
--'Month to Date'
INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
VALUES (
@i,
CONVERT(VARCHAR,DatePart(m, @EndDate)) + '/1/' + CONVERT(CHAR(4),DatePart(yyyy, @EndDate)),
CONVERT(CHAR(10),@EndDate,110),
Right( '0' + convert(varchar, @i), 2) + ';MonthToDate','Month To Date'
)
SET @i = @i + 1
RETURN
END
GO
GRANT SELECT ON dbo.DateRanges TO PUBLIC
GO

DateRanges SQL Script

Leave a Reply

Your email address will not be published. Required fields are marked *