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

 
ċ
udf_CalculateUsefulDateRanges.sql
(5k)
Andy Hughes,
Jun 25, 2012, 8:31 AM
Comments