CODEWITHSUNDEEP
sqlsql-serversql-server-2008
coopertkm
coopertkm

Reputation: 757

How to check if a datetime variable occurs in the past twelve months

What I am trying to do is to see if a datetime variable has the value of a date at anytime in the past twelve months.

I tried using the DATEDIFF function but then I quickly discovered that this has rounding problems (a comparison between 31st DEC/1st JAN would count as 1 month for example, despite the fact only a day has passed between them)

To make this more accurate I could use -365 days as a parameter rather than -12 months however this still gives the problem of not taking into account leap years (not a common scenario but still one I'd ideally avoid!)

Any ideas as to how I can best go about this?

Upvotes: 0

Views: 271

Answers (3)

gbn
gbn

Reputation: 432471

Use DATEADD instead to subtract a year from today and compare that

@datetimevariable >= DATEADD(year, -1, GETDATE())

Note: you don't need to compare the upper bound (GETDATE()) unless you have dates in the future.

Also, do you want exactly one year down to the hour and minute? If not, use this which is safe for datatype precedence if @datetimevariable is datetime or datetime2, and you need this is a WHERE clause or such

@datetimevariable >= CAST(DATEADD(year, -1, GETDATE()) as date)

This works for leap years

SELECT DATEADD(YEAR, -1, '20120301'), DATEADD(YEAR, -1, '20120229')

2011-03-01 00:00:00.000
2011-02-28 00:00:00.000

DATEDIFF works on specific boundary: start of month, start of year, midnight etc which is why it fails in this case

Upvotes: 1

jle
jle

Reputation: 9489

The problem with leap years can be solved with the DATEADD (datepart , number , date ) function. You can specify minus one year from the current date.

where myDate between DATEADD (year , -1 , GETDATE() ) and GETDATE()

Upvotes: 1

Ian P
Ian P

Reputation: 1724

DATEDIFF counts the number of partitions between the two dates, sorry to say there is no rounding issue, it does exactly what it says on the tin. YOUR best solution is probably to used DATEADD

WHERE MyVal BETWEEN GETDATE() AND DATEADD(YEAR, -1, GETDATE())

Upvotes: 0

Related Questions