|
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   
0 Comments
|
|||
| Microsoft Data & AI | Database administration |
RSS Feed
