SQL Server - DATEDIFF rounding error

Question

In my sql statement, I need to retrieve rows which datediff more than 3 months.

But I found that it seems have rounding problem such as

From Date: 2010-09-09
To Date: 2010-12-01

select datediff(month,' 2010-09-09', '2010-12-01')

It returns 3 for result.

How to fix it ? thanks.

regards, Joe

Can you elaborate a bit: What value were you expecting? and why? — Lee, Sep 30 '11 at 04:28
possible duplicate of [Calculating number of full months between two dates in SQL](http://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql) — mellamokb, Sep 30 '11 at 04:34

score 2 · Accepted Answer · answered Sep 30 '11 at 04:27

2

You could use

datediff(day,@d1,@d2) >= 90

answered Sep 30 '11 at 04:27

Matt

3,638
2
26
33

Technically not quite correct. It would be better to datediff the months, then compare the day manually. – mellamokb Sep 30 '11 at 04:29
2

horseshoes and hand-grenades :) – Matt Sep 30 '11 at 04:34

score 1 · Answer 2 · edited May 23 '17 at 10:34

Datediff(month, date1, date2) will only compare the difference between the month parts and does not take days into account.

In order to calculate the real number of months between the dates, you will have to do some manual work.

This answer

looks to be a match to what you are asking but you may have to modify depending on your specific definition of what constitutes a 'month' difference (partial months included?).

SQL Server - DATEDIFF rounding error

2 Answers2