Date and Time - Calculate Common Date Ranges

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 13 Sep 2006

Description

This function was written several years before the master calendar table code started to take shape, and the results could quite easily be appended to its definition. However, this would mean adding 22 extra columns to the table.

At the time that this function was written, the requirements were better fulfilled by a Table Valued Function, and I feel that this is probably still the case (this can be used to directly populate an SSRS report pick list) so I haven't converted it.

Basically, the function uses a data parameter as a seed date and calculates start and end dates for the following named date ranges:

Today
This Week
This Month
This Year
Tomorrow
Next Week
Next Month
Next Year
Yesterday
Last Week
Last Month
Last Year

The dates can be directly used as the date parameters in a BETWEEN statement.

Code

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())