CODEWITHSUNDEEP
sql
Wilest
Wilest

Reputation: 1860

sql date parameter in query

I've got a query and I want to add parameters to calculate the moving annual (the figures for the last 12 months). I'm trying to subtract 12 months from today, so if for instance today is 1 August 2012 then my @StartDate should be '2011-09-01' and my @EndDate should be '2012-08-31'. So how do I change set my parameters to accommodate this?

declare @StartDate DATE
declare @EndDate   DATE
SET @StartDate = DATEADD(MONTH, -12, '2012-08-01')
SET @EndDate = DATEADD(MONTH, +1, '2012-08-01')

Upvotes: 5

Views: 71909

Answers (4)

Jonathan Solomon
Jonathan Solomon

Reputation: 11

This was a few years ago but still not answered properly. The poster wants to find 12 months back from today, where "today" is dynamic. All prior answers start with a hard-coded start date instead of GETDATE()

DECLARE 
@StartDate datetime,
@EndDate datetime

Set @StartDate = DATETIMEFROMPARTS(YEAR(GETDATE())-1, MONTH(GETDATE()), DAY(GETDATE()), 0, 0, 0, 0); Use midnight or a time that matches your business case.
Set @EndDate =  DATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), 23, 59, 59, 0); -- modify to match the times you are looking for.

Select * from Mydbase
WHERE
      table.dateField between @StartDate and @EndDate

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try tiis:

declare @Date DATE='2012-08-05'
select convert(date,DateAdd(Month, DateDiff(Month, 0, @Date)-11,0)) StartDate ,
                       convert(date,DateAdd(day,-1,DateAdd(Month,1,
                       DateAdd(Month, DateDiff(Month, 0, @Date),0)))) EndDate

result:

StartDate       EndDate
2011-09-01  2012-08-31

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

DECLARE
  @InputDate DATE,
  @StartDate DATE,
  @EndDate   DATE

SET
  @InputDate = '2012-08-01'

SET
  @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @InputDate) - 11, 0)

SET
  @EndDate   = DATEADD(DAY, -1, DATEADD(MONTH, 12, @StartDate))

EDIT:

However, I do not recomend using '2011-09-01' to '2012-08-31' as a representation of a year. Instead, use '2011-09-01' to '2012-09-01' in the following way...

WHERE
      table.dateField >= '2011-09-01'
  AND table.dateField <  '2012-09-01'

This works for all Date and DateTime data types. Even if the value in dateField is 2:30pm on 31st August, this will still work. It's a one size fits all approach and makes it much more difficult to make mistakes on date and time boundaries.

Upvotes: 7

Yaroslav
Yaroslav

Reputation: 6534

Add this after your last SET command:

SET @EndDate = DATEADD(DAY, -1, @EndDate)

Or substitute the 2nd SET by:

SET @EndDate = DATEADD(DAY, -1,DATEADD(MONTH, +1, '2012-08-01'))

Upvotes: 0

Related Questions