Get The Difference Between Two Dates Both in Months and Days in Sql

Calculating number of full months between two dates in SQL

The original post had some bugs... so I re-wrote and packaged it as a UDF.

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

SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-15') as MonthSep -- =0
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-16') as MonthSep -- =1
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-06-16') as MonthSep -- =2

How to use DATEDIFF to return year, month and day?

Here's my solution to Eric's function:

DECLARE @getmm INT
DECLARE @getdd INT

SET @yy = DATEDIFF(yy, @dstart, @dend)
SET @mm = DATEDIFF(mm, @dstart, @dend)
SET @dd = DATEDIFF(dd, @dstart, @dend)
SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

RETURN (
  Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@getmm) + 'month'  + Convert(varchar(10),@getdd) + 'day'
)

Good call on the use of ABS to handle if the start date is after the end date.

This:

WITH ex_table AS (
  SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM ex_table t

..or non-CTE using for SQL Server 2000 and prior:

SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM (SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime') t

...will return:

result
----------------------
2 year 2 month 28 day

Reference: DATEDIFF

Calculate period between two dates in months and days

You have to add one day (using DATEADD) to the second date (@date2):

-- first example
DECLARE @date1 DATETIME = '2009-01-01 00:00:00'
DECLARE @date2 DATETIME = '2010-06-30 00:00:00'

SELECT CAST(DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)) AS VARCHAR(6)) + ' Months ' + CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)), @date1), DATEADD(DAY, 1, @date2)) AS VARCHAR(2)) + ' Days'

-- second example
DECLARE @date1 DATETIME = '2020-01-01 00:00:00'
DECLARE @date2 DATETIME = '2020-01-31 00:00:00'

SELECT CAST(DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)) AS VARCHAR(6)) + ' Months ' + CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)), @date1), DATEADD(DAY, 1, @date2)) AS VARCHAR(2)) + ' Days'

demo on dbfiddle.uk

Have a look at the following examples:

DATEDIFF of 2020-01-01 and 2020-01-01 is 0.

(max. difference between 2020-01-01 00:00:00 and 2020-01-01 23:59:59 is equals to 0 day, 23 hours, 59 minutes and 59 seconds.
DATEDIFF of 2020-01-01 and 2020-01-02 is 1.

(max. difference between 2020-01-01 00:00:00 and 2020-01-02 23:59:59 is equals to 1 day, 23 hours, 59 minutes and 59 seconds.

So you can't count the last day as day in the difference since the day isn't over.

how do I get the EXACT number of months between two dates?

You could do the calculation yourself in the following way:

DECLARE @startdate date = '1/1/2014'
DECLARE @enddate date = '12/30/2014'

DECLARE @startday int = DATEPART(DAY, @startdate)
DECLARE @endday int = DATEPART(DAY, @enddate)
DECLARE @startdateBase date = DATEADD(DAY, 1 - @startday, @startdate)
DECLARE @enddateBase date = DATEADD(DAY, 1 - @endday, @enddate)

DECLARE @deciMonthDiff float = CAST(DATEDIFF(MONTH, @startdate, @enddate) AS float) -
  (@startday - 1.0) / DATEDIFF(DAY, @startdateBase, DATEADD(MONTH, 1, @startdateBase)) +
  (@endday - 1.0) / DATEDIFF(DAY, @enddateBase, DATEADD(MONTH, 1, @enddateBase))

SELECT @deciMonthDiff

This calculates the @deciMonthDiff to be 11.935483870967.

Of course you can "inline" this as much as you want in order to avoid all the middle declarations.

The idea is to calculate the total month diff, then subtract the relative part of the first & last month depending on the actual day.

Month difference between two dates in sql server

Try this

SELECT CASE WHEN DATEDIFF(d,'2013-09-01', '2013-11-15')>30 THEN DATEDIFF(d,'2013-09-01', '2013-11-15')/30.0 ELSE 0 END AS 'MonthDifference'

SELECT DATEDIFF(DAY, '2013-09-01', '2013-11-15') / 30.436875E