CODEWITHSUNDEEP
sqldatediff
JBone
JBone

Reputation: 3203

Battling Datediff in SQL

I am writing a little query in SQL and am butting heads with an issue that it seems like someone must have run into before. I am trying to find the number of months between two dates. I am using an expression like ...

DATEDIFF(m,{firstdate},{seconddate})

However I notice that this function is tallying the times the date crosses the monthly threshold. In example... 

DATEDIFF(m,3/31/2011,4/1/2011) will yield 1  
DATEDIFF(m,4/1/2011,4/30/2011) will yield 0 
DATEDIFF(m,3/1/2011,4/30/2011) will yield 1

Does anyone know how to find the months between two dates more-so based upon time passed then times passed the monthly threshold?

Upvotes: 5

Views: 850

Answers (4)

Chains
Chains

Reputation: 13157

DATEDIFF is like this by design. When evaluating a particular time measurement (like months, or days, etc.), it considers only that measurement and higher values -- ignoring smaller ones. You'll run into this behavior with any time measurement. For example, if you used DATEDIFF to calculate days, and had one date a few seconds before midnight, and another date a few seconds after midnight, you'd get a "1" day difference, even though the two dates were only a few seconds apart.

DATEDIFF is meant to give a rough answer to questions, like this:

Question: how many years old are you? Answer: some integer. You don't say "I'm 59 years, 4 months, 17 days, 5 hours, 35 minutes and 27 seconds old". You just say "I'm 59 years old". That's DATEDIFF's approach too.

If you want an answer that's tailored to some contextual meaning (like your son who says "I'm not 8! I'm 8 and 3-quarters!, or I'm almost 9!), then you should look at the next-smallest measurement and approximate with it. So if it's months you're after, then do a DATEDIFF on days or hours instead, and try to approximate months however it seems most relevant to your situation (maybe you want answers like 1-1/2 months, or 1.2 months, etc.) using CASE / IF-THEN kinds of logic.

Upvotes: 1

JBone
JBone

Reputation: 3203

DATEDIFF(m,{firstdate},ISNULL({seconddate},GETDATE())) - CASE 
                                                         WHEN DATEPART(d,{firstdate}) >= DATEPART(d,ISNULL({seconddate},GETDATE()))
                                                         THEN 1
                                                         ELSE 0

Upvotes: 1

a1ex07
a1ex07

Reputation: 37364

The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. ...
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000'); ....

(from DATEDIFF, section datepart Boundaries ). If you are not satisfied by it, you probably need to use days as unit as proposed by martin clayton

Upvotes: 1

martin clayton
martin clayton

Reputation: 78105

If you want to find some notional number of months, why not find the difference in days, then divide by 30 (cast to FLOAT as required). Or 30.5-ish perhaps - depends on how you want to handle the variable month length throughout the year. But perhaps that's not a factor in your particular case.

Upvotes: 2

Related Questions