DROP FUNCTION dbo.udf_CalculateUsefulDateRanges GO
CREATE FUNCTION dbo.udf_CalculateUsefulDateRanges (@DateRangeSeed DATETIME) RETURNS @DateRange TABLE ( DateRangeID INT IDENTITY (1,1), DateRangeType VARCHAR(30), StartDate DATETIME, EndDate DATETIME ) AS
/**************************************************************************** Purpose: A supplemental function to be used alongside the Calendar table The calendar table contains one row for each day with precalcualted attributes This function returns a slection of common date ranges using the date parameter as the starting point for calculating the offsets. These could have been added to the calendar table as columns (24 new columns) but this would have bloated the storage a bit. Also, when I wrote this, the requriements for usage made it more suited to a TVF, and I feel that this is likely to still be the case so I left it as a function.
Author: ChillyDBA History 13 Sep 2006 *****************************************************************************/
BEGIN
-- local variables for output DECLARE @StartDate DATETIME, @EndDate DATETIME, @Today DATETIME
-- calculate the 0-hour date for today SELECT @Today = CONVERT(DATETIME, CONVERT(VARCHAR(11), GETDATE(),113))
-- calculate and save the TODAY date range SELECT @StartDate = @Today, @EndDate = DATEADD(dd, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Today', @StartDate, @EndDate)
-- calculate and save the THIS WEEK date range
SELECT @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR(11), DATEADD(dd, (DATEPART(DW, @Today) - 1) * -1, @Today), 113)), @EndDate = DATEADD(ww, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('This Week', @StartDate, @EndDate)
-- calculate and save the THIS MONTH date range SELECT @StartDate = CONVERT(DATETIME, '1 ' + DATENAME(MONTH, @Today) + ' ' + CONVERT(VARCHAR,DATEPART(yy, @Today ))), @EndDate = DATEADD(mm, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('This Month', @StartDate, @EndDate)
-- calculate and save the THIS YEAR date range SELECT @StartDate = CONVERT(DATETIME, '1 Jan ' + CONVERT(VARCHAR(4), DATEPART(yy, @Today))), @EndDate = DATEADD(yy, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('This Year', @StartDate, @EndDate)
-- calculate and save the TOMORROW date range SELECT @StartDate = DATEADD(dd, 1, @Today), @EndDate = DATEADD(dd, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Tomorrow', @StartDate, @EndDate)
-- calculate and save the NEXT WEEK date range SELECT @StartDate = DATEADD(ww, 1, CONVERT(DATETIME, CONVERT(VARCHAR(11), DATEADD(dd, (DATEPART(DW, @Today) - 1) * -1, @Today), 113))), @EndDate = DATEADD(ww, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Next Week', @StartDate, @EndDate)
-- calculate and save the NEXT MONTH date range SELECT @StartDate = DATEADD(mm, 1, CONVERT(DATETIME, '1 ' + DATENAME(MONTH, @Today) + ' ' + CONVERT(VARCHAR,DATEPART(yy, @Today )))), @EndDate = DATEADD(mm, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Next Month', @StartDate, @EndDate)
-- calculate and save the NEXT YEAR date range SELECT @StartDate = DATEADD(yy, 1, CONVERT(DATETIME, '1 Jan ' + CONVERT(VARCHAR(4), DATEPART(yy, @Today)))), @EndDate = DATEADD(yy, 1, @StartDate) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Next Year', @StartDate, @EndDate)
-- calculate and save the YESTERDAY date range SELECT @StartDate = DATEADD(dd, -1, @Today), @EndDate = @Today INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Yesterday', @StartDate, @EndDate)
-- calculate and save the LAST WEEK date range SELECT @StartDate = DATEADD(ww, -1, CONVEERT(DATETIME, CONVERT(VARCHAR(11), DATEADD(dd, (DATEPART(DW, @Today) - 1) * -1, @Today), 113))), @EndDate = CONVERT(DATETIME, CONVERT(VARCHAR(11), DATEADD(dd, (DATEPART(DW, @Today) - 1) * -1, @Today), 113)) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Last Week', @StartDate, @EndDate)
-- calculate and save the LAST MONTH date range SELECT @StartDate = DATEADD(mm, -1, CONVERT(DATETIME, '1 ' + DATENAME(MONTH, @Today) + ' ' + CONVERT(VARCHAR,DATEPART(yy, @Today )))), @EndDate = CONVERT(DATETIME, '1 ' + DATENAME(MONTH, @Today) + ' ' + CONVERT(VARCHAR,DATEPART(yy, @Today ))) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Last Month', @StartDate, @EndDate)
-- calculate and save the LAST YEAR date range SELECT @StartDate = DATEADD(yy, -1, CONVERT(DATETIME, '1 Jan ' + CONVERT(VARCHAR(4), DATEPART(yy, @Today)))), @EndDate = CONVERT(DATETIME, '1 Jan ' + CONVERT(VARCHAR(4), DATEPART(yy, @Today))) INSERT @DateRange(DateRangeType, StartDate, EndDate) VALUES ('Last Year', @StartDate, @EndDate)
-- now reduce all end dates by 2ms to ensure they can be used in an BETWEEN statement -- (1ms will have no effect as accuracy of datetime type causes it to be rounded back up) UPDATE @DateRange SET EndDate = DATEADD(ms, -2, EndDate) RETURN END
--select * from dbo.udf_CalculateUsefulDateRanges(GETDATE())
|