• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

Database administration

t-SQL Date Calculations

6/16/2015

0 Comments

 
I was asked today for help with defaulting SSRS report date parameters.  In response I am sharing this snippet of t-SQL that I put together back in 2008 (yes, seven years ago!), but that still works today.  Certainly you can do these date calculations in MDX and DAX, but how easy is this?  Having these values return in a UDF (user defined function) also allows you to easily consume this logic in USPs (user stored procedures) for complex business calculations.

Inserted below is a correctly formatted *.sql file for download.

USE [YourDatabaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Author:       Delora Bradish
Date Created: February 22, 2008
Description:  Get date comparisons
Where Used:   SSRS Report defaults
Notes:     
To Run:
SELECT * FROM fnGetDateRanges ('2/29/2012')
SELECT * FROM fnGetDateRanges (GetDate())

SELECT top 10 *
FROM dbo.Invoice i
WHERE i.inv_DateInvoice Between (SELECT FirstDayPriorMonth3 FROM fnGetDateRanges(GetDate())) and GetDate()


Revision History:   
*/

CREATE FUNCTION [dbo].[fnGetDateRanges] (@i_Date DateTime)
RETURNS table
AS
RETURN

(
SELECT @i_Date as iDate
, CurrentDate = Convert(DateTime,Convert(varchar(8),@i_Date,112),1)
, CurrentDay = Day(@i_Date)
, CurrentWeek = DatePart(Week, @i_Date)
, CurrentMonth = Month(@i_Date)
, CurrentYearMonth = Convert(int,Convert(char(4),Year(@i_Date)) + RIGHT(RTrim('00' + Convert(char(2),Month(@i_Date))),2))
, PriorDay1 = Convert(DateTime,Convert(varchar(8),@i_Date -1,112),1)
, PriorWeek = DatePart(Week, @i_Date - 7)
, PriorMonth = DatePart(mm,dateadd(day,(day(@i_date)-1)*-1,dateadd(month,-1,@i_date))) 

--you may want to trap for leap years here
, PriorYear1 = DatePart(yy, @i_Date - 365)
, PriorYear2 = DatePart(yy, @i_Date - 730)
, PriorYear3 = DatePart(yy, @i_Date - 1086)
, PriorYear4 = DatePart(yy, @i_Date - 1451)
, PriorYear5 = DatePart(yy, @i_Date - 1816)
, PriorYear6 = DatePart(yy, @i_Date - 2181) 

, SameDayLastYear = CASE WHEN Right(Convert(char(8),@i_Date,112),4) = '0229'
                         THEN @i_Date - 366
                         ELSE @i_Date - 365
                    END 

, FirstDayCurrentMonth = DATEADD(mm, DATEDIFF(mm, 0, @i_Date), 0)
, FirstDayCurrentWeek = DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0)
, FirstDayCurrentYear = DATEADD(yy, DATEDIFF(yy, 0, @i_Date), 0)
, LastDayCurrentMonth = dateadd(mm, datediff(mm, 0, dateadd(mm, 1, @i_date)), -1)
, LastDayCurrentWeek = dateadd(ww, datediff(ww, 0, dateadd(ww, 1, @i_date)), -1)
, LastDayCurrentYear = dateadd(yy, datediff(yy, 0, dateadd(yy, 1, @i_date)), -1) 

, FirstDayPriorMonth1 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-1,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-1,@i_Date))))

, FirstDayPriorMonth2 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-2,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-2,@i_Date))))

, FirstDayPriorMonth3 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-3,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-3,@i_Date))))

, FirstDayPriorMonth4 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-4,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-4,@i_Date))))

, FirstDayPriorMonth5 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-5,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-5,@i_Date))))

, FirstDayPriorMonth6 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-6,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-6,@i_Date))))

, FirstDayPriorMonth7 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-7,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-7,@i_Date))))

, FirstDayPriorMonth8 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-8,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-8,@i_Date))))

, FirstDayPriorMonth0 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-9,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-9,@i_Date))))

, FirstDayPriorMonth10 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-10,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-10,@i_Date))))

, FirstDayPriorMonth11 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-11,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-11,@i_Date))))

, FirstDayPriorMonth12 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-12,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-12,@i_Date))))

, FirstDayPriorMonth13 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-13,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-13,@i_Date)))) 

--this is the prior YYYYMM from the date passed, not the prior year's month number
, PriorMonth1 = Month(DateAdd(month,-1,@i_Date))
, PriorMonth2 = Month(DateAdd(month,-2,@i_Date))
, PriorMonth3 = Month(DateAdd(month,-3,@i_Date))
, PriorMonth4 = Month(DateAdd(month,-4,@i_Date))
, PriorMonth5 = Month(DateAdd(month,-5,@i_Date))
, PriorMonth6 = Month(DateAdd(month,-6,@i_Date))
, PriorMonth7 = Month(DateAdd(month,-7,@i_Date))
, PriorMonth8 = Month(DateAdd(month,-8,@i_Date))
, PriorMonth9 = Month(DateAdd(month,-9,@i_Date))
, PriorMonth10 = Month(DateAdd(month,-10,@i_Date))
, PriorMonth11 = Month(DateAdd(month,-11,@i_Date))
, PriorMonth12 = Month(DateAdd(month,-12,@i_Date))
, PriorMonth13 = Month(DateAdd(month,-13,@i_Date))             

, PriorYearMonth1 = Convert(int,Convert(char(4),Year(DateAdd(month,-1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-1,@i_Date)))),2))

, PriorYearMonth2 = Convert(int,Convert(char(4),Year(DateAdd(month,-2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-2,@i_Date)))),2))

, PriorYearMonth3 = Convert(int,Convert(char(4),Year(DateAdd(month,-3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-3,@i_Date)))),2))

, PriorYearMonth4 = Convert(int,Convert(char(4),Year(DateAdd(month,-4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-4,@i_Date)))),2))

, PriorYearMonth5 = Convert(int,Convert(char(4),Year(DateAdd(month,-5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-5,@i_Date)))),2))

, PriorYearMonth6 = Convert(int,Convert(char(4),Year(DateAdd(month,-6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-6,@i_Date)))),2))

, PriorYearMonth7 = Convert(int,Convert(char(4),Year(DateAdd(month,-7,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-7,@i_Date)))),2))

, PriorYearMonth8 = Convert(int,Convert(char(4),Year(DateAdd(month,-8,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-8,@i_Date)))),2))

, PriorYearMonth9 = Convert(int,Convert(char(4),Year(DateAdd(month,-9,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-9,@i_Date)))),2))

, PriorYearMonth10 = Convert(int,Convert(char(4),Year(DateAdd(month,-10,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-10,@i_Date)))),2))

, PriorYearMonth11 = Convert(int,Convert(char(4),Year(DateAdd(month,-11,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-11,@i_Date)))),2))

, PriorYearMonth12 = Convert(int,Convert(char(4),Year(DateAdd(month,-12,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-12,@i_Date)))),2))

, PriorYearMonth13 = Convert(int,Convert(char(4),Year(DateAdd(month,-13,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-13,@i_Date)))),2))             

--this is the next YYYYMM from the date past, not next year's month number
, NextYearMonth1 = Convert(int,Convert(char(4),Year(DateAdd(month,+1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+1,@i_Date)))),2))

, NextYearMonth2 = Convert(int,Convert(char(4),Year(DateAdd(month,+2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+2,@i_Date)))),2))

, NextYearMonth3 = Convert(int,Convert(char(4),Year(DateAdd(month,+3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+3,@i_Date)))),2))

, NextYearMonth4 = Convert(int,Convert(char(4),Year(DateAdd(month,+4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+4,@i_Date)))),2))

, NextYearMonth5 = Convert(int,Convert(char(4),Year(DateAdd(month,+5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+5,@i_Date)))),2))

, NextYearMonth6 = Convert(int,Convert(char(4),Year(DateAdd(month,+6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+6,@i_Date)))),2))             

, FirstDayPriorWeek1 = DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0) - 7 

, FirstDayPriorYear1 = DATEADD(yy, DATEDIFF(yy, 366, @i_Date), 0)
, FirstDayPriorYear2 = DATEADD(yy, DATEDIFF(yy, 731, @i_Date), 0)
, FirstDayPriorYear3 = DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), 0)
, FirstDayPriorYear4 = DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), 0)
, FirstDayPriorYear5 = DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), 0)
, FirstDayPriorYear6 = DATEADD(yy, DATEDIFF(yy, 2191, @i_Date), 0) 

, LastDayPriorMonth1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@i_date), 0)),112),1)

--this calculation assumes a Monday (start) to Sunday (end) week
, LastDayPriorWeek1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(ww, DATEDIFF(ww,0,@i_date), 0)),112),1)

, LastYearLastDayPriorMonth1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,365,@i_date), 0)),112),1)             

--you may want to trap for leap years here
, LastDayPriorYear1 = DATEADD(yy, DATEDIFF(yy, 1, @i_Date), -1)
, LastDayPriorYear2 = DATEADD(yy, DATEDIFF(yy, 366, @i_Date), -1)
, LastDayPriorYear3 = DATEADD(yy, DATEDIFF(yy, 731, @i_Date), -1)
, LastDayPriorYear4 = DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), -1)
, LastDayPriorYear5 = DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), -1)
, LastDayPriorYear6 = DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), -1) 

, PriorYTD1 = DATEADD(yy, -1, Convert(varchar(25),@i_Date,112))
, PriorYTD2 = DATEADD(yy, -2, Convert(varchar(25),@i_Date,112))
, PriorYTD3 =  DATEADD(yy, -3, Convert(varchar(25),@i_Date,112))
, PriorYTD4 = DATEADD(yy, -4, Convert(varchar(25),@i_Date,112))
, PriorYTD5 = DATEADD(yy, -5, Convert(varchar(25),@i_Date,112))
, PriorYTD6 = DATEADD(yy, -6, Convert(varchar(25),@i_Date,112))             

, NextYTD1 = DATEADD(yy, +1, Convert(varchar(25),@i_Date,112))
, NextYTD2 = DATEADD(yy, +2, Convert(varchar(25),@i_Date,112))
, NextYTD3 = DATEADD(yy, +3, Convert(varchar(25),@i_Date,112))
, NextYTD4 = DATEADD(yy, +4, Convert(varchar(25),@i_Date,112))
, NextYTD5 = DATEADD(yy, +5, Convert(varchar(25),@i_Date,112))
, NextYTD6 = DATEADD(yy, +6, Convert(varchar(25),@i_Date,112)) 

, FirstDayYearPriorMonth = CAST ('1/1/' + Cast(year(@i_Date) - case month(@i_Date) WHEN 1 THEN 1 ELSE 0 END as varchar(10)) as datetime)      

)
Example Output

Picture
Picture
dba_fngetdateranges.sql
File Size: 20 kb
File Type: sql
Download File

0 Comments

    Categories

    All
    Columnstore
    Connecting
    Date & Time
    DBA
    Extended Events
    Indexing
    Installation
    Oracle
    Performance
    SQL 2016
    SQL Developer
    SSAS
    SSIS
    SSRS
    T-SQL

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About