Calculate Common Date Ranges

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