CODEWITHSUNDEEP
sql.netsql-serverdatabase
vmb
vmb

Reputation: 3028

Issue with DateDiff function in SQL Server

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.

Upvotes: 1

Views: 2030

Answers (3)

Jatin
Jatin

Reputation: 1

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`

Upvotes: 0

Kishore
Kishore

Reputation: 5881

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

Upvotes: 1

cha
cha

Reputation: 10411

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 |

Upvotes: 2

Related Questions