Help with DateAdd/DateDiff for parameter defaults 1

[dianemarie]

Hi, I found the code below to put into a dataset query, then I pull the CurrentStartDate and CurrentEndDate as defaults into my parameters. Works really good for the first date of current month, and the last second of the prior day of current month. Exactly what I wanted (I'd like to format the parameter not to show time, but I haven't really looked at that yet.) My report is a 2 year comparison, so what I could use some help on is how to get the same dates for one year ago. (Ex: CurrentStart = 4/1/09, CurrentEnd = 4/27/09, PriorStart = 4/1/08, PriorEnd = 4/27/08) You see where I'm going. Been playing with DateDiff but I'm not getting it, despite printing off the Microsoft explanations. Thank you so much if you can help.

select dateadd(mm,datediff(mm,0,getdate()),0) [CurrentStart],

dateadd(s,-1,dateadd(dd,datediff(dd,0,getdate()),0)) [CurrentEnd],

 

[RiverGuy]

Using your first example, here are some variations to get "the previous year."

--Current Year
select dateadd(mm,datediff(mm,0,getdate()),0)

--Prior Year / Same Day of Week
select DATEADD(ww, -52, dateadd(mm,datediff(mm,0,getdate()),0))

--365 Days Prior
select DATEADD(day, -365, dateadd(mm,datediff(mm,0,getdate()),0))

--Prior Year / Same Day of Month
select DATEADD(year, -1, dateadd(mm,datediff(mm,0,getdate()),0))

 

[dianemarie]

Hi RG, both your solutions #3 & #4 got me the first day of the current month for one year ago, which is great. However, solution #2 returns 4/2/08. I'm looking for the previous end of day, one year ago (from the day the report was run). So if I ran the report today, I would expect to see 4/28/08 11:59:59 PM. I am still Googling and am amazed at the seemingly hundreds of different ways to skin this one dang cat. I think these functions are very confusing...

 

[RiverGuy]

I posted those up for general reference as there are several ways to consider the previous year. #2 is useful when you are looking at the day level and you want to analyze the same date last year based on the day of week. For example, comparing Saturday to Saturday is more apples to apples than Saturday to Sunday in some businesses.

For your other date, you can add to your existing query:

SELECT DATEADD(year, -1, dateadd(s,-1,dateadd(dd,datediff(dd,0,getdate()),0)))

 

[dianemarie]

Thanks so much! You rock.