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