Issue with DateDiff function in SQL Server

Question

I am facing an issue for finding exact date difference in Months.

My requirement is like

 difference in months between two dates in 2015-01-25 00:00:00.000 and  2015-04-25 00:00:00.000   should be 3 

 difference in months between two dates 2015-01-25 00:00:00.000 and  2015-04-26 00:00:00.000   should be > 3 


   SELECT DATEDIFF(MONTH, '2015-01-25 00:00:00.000', '2015-04-28 00:00:00.000')

if you use DATEDIFF Function,then both of the above examples will return same value 3. ie SELECT DATEDIFF(MONTH, '2015-01-25 00:00:00.000', '2015-04-26 00:00:00.000') is 3.But i need result which is greater than 3.

How can i implement above requirement using a single select query.

What is the expected value when doing DATEDIFF(MONTH, '2015-01-25 00:00:00.000', '2015-04-26 00:00:00.000')? Do you want it to be 3.xx? — ekad
– ekad, Commented Dec 3, 2014 at 5:02
Because of variable length months, you'll always get some unintuitive (to you, for whatever you're currently working on) results when you try to calculate in terms of months. Thinking especially about dates at the end of months, you might end up with two comparisons of two dates that are 30 days apart where, depending on the months involved, you might get results like "1 month, 1 day", "2 months exactly" and "2 months and 1 day". — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 3, 2014 at 8:22
I know this is old but I'm putting this out there because I would have appreciated seeing this myself. I had a similar but slightly different issue, where the number of years (2017 vs. 2020) was coming up as 3 - despite the month in the 2017 year being later. So December 2017 was coming up as 3 years different from March 2020, which I suppose is technically correct if you are counting years (but really we are usually trying to count how much time has passed, and 3 years have not actually passed...) (cont.) — RiSt
– RiSt, Commented Nov 9, 2020 at 20:59

cha · Accepted Answer · 2014-12-03 05:24:58Z

2

You need to calculate the months and then you need to advance the start date by the number of months and calculate the days, like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

declare @f datetime, @t datetime
select @f='2015-01-25 00:00:00.000', @t='2015-04-28 00:00:00.000'
SELECT DATEDIFF(MONTH, @f, @t) as m, 
datediff(d, dateadd(month, DATEDIFF(MONTH, @f, @t), @f), @t) as d,
DATEDIFF(MONTH, @f, @t) + convert(float, datediff(d, dateadd(month, DATEDIFF(MONTH, @f, @t), @f), @t)) / 30.0 as md

Results:

| M | D |  MD |
|---|---|-----|
| 3 | 3 | 3.1 |

answered Dec 3, 2014 at 5:24

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kishore · Accepted Answer · 2014-12-03 05:39:00Z

1

declare @s datetime, @e datetime
select @s='2015-01-25 00:00:00.000', @e='2015-04-28 00:00:00.000'
 SELECT ceiling(cast(cast(DATEDIFF(MONTH, @s,@e) as varchar)+'.'+cast(-(DATEPART(dd,@s)-DATEPART(dd, @e)) as varchar) as float)) as Month

Result

Month
 4

edited Dec 3, 2014 at 5:39

answered Dec 3, 2014 at 5:33

Kishore

5,9215 gold badges32 silver badges55 bronze badges

Comments

Jatin · Accepted Answer · 2020-05-18 09:54:14Z

The perfect solution to find the actual Months between two dates is as below:

CREATE FUNCTION FullMonthsSeparation 
(
    @DateA DATETIME,
    @DateB DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT

    DECLARE @DateX DATETIME
    DECLARE @DateY DATETIME

    IF(@DateA < @DateB)
    BEGIN
        SET @DateX = @DateA
        SET @DateY = @DateB
    END
    ELSE
    BEGIN
        SET @DateX = @DateB
        SET @DateY = @DateA
    END

    SET @Result = (
                    SELECT 
                    CASE 
                        WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
                        THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
                        ELSE DATEDIFF(MONTH, @DateX, @DateY)
                    END
                    )

    RETURN @Result
END
GO````


`-- AS eaxmple 
declare @a datetime = getdate()
declare @b datetime = getdate()-59

--less than 60 days i.e. 1 months
select dbo.FullMonthsSeparation (@a,@b) AS Months

SET @a = getdate()
SET @b = getdate()-61

--More than 60 days i.e. 2 months
select dbo.FullMonthsSeparation (@a,@b) AS Months`

Collectives™ on Stack Overflow

Issue with DateDiff function in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related