Calculating Number of Full Months Between Two Dates 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

SQL Server - Count difference months between two dates

If I understood your question correctly, This will be my suggestion:

Just add 1 to your DATEDIFF output.

SELECT DATEDIFF(mm, '2015-11-01', '2015-12-01') +1

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.

How to count the number of months between two dates SQL

In Postgres you can use the generate_series(date,date,interval) function to construct the date table and then simply group by and count to determine how many times each month occurs in the range.

SELECT EXTRACT(month from d) as month, count(*)
FROM generate_series('2010-8-24'::date, '2012-3-29'::date, '1 month'::interval) d
GROUP BY 1 
ORDER BY 1;

 month | count 
-------+-------
     1 |     2
     2 |     2
     3 |     2
     4 |     1
     5 |     1
     6 |     1
     7 |     1
     8 |     2
     9 |     2
    10 |     2
    11 |     2
    12 |     2
(12 rows)

Count full months between two dates

Test setup

With some sample rows (should be provided in the question):

CREATE TABLE test (
   test_id serial PRIMARY KEY
 , date1   date NOT NULL
 , date2   date NOT NULL
);

INSERT INTO test(date1, date2)
VALUES
   ('2012-03-20', '2012-01-05')  -- 2012-02 lies in between
 , ('2012-01-20', '2012-03-05')  -- 2012-02 (reversed)
 , ('2012-05-06', '2012-05-05')  -- nothing
 , ('2012-05-01', '2012-06-30')  -- still nothing
 , ('2012-08-20', '2012-11-05')  -- 2012-09 - 2012-10
 , ('2012-11-20', '2013-03-05')  -- 2012-12 - 2013-02
;

Postgres 9.3 or newer

Use a LATERAL join:

SELECT to_char(mon, 'YYYY') AS year
     , to_char(mon, 'MM')   AS month
     , count(*) AS ct
FROM  (
   SELECT date_trunc('mon',    least(date1, date2)::timestamp) + interval '1 mon' AS d1
        , date_trunc('mon', greatest(date1, date2)::timestamp) - interval '1 mon' AS d2
   FROM   test
   ) sub1
 , generate_series(d1, d2, interval '1 month') mon  -- implicit CROSS JOIN LATERAL
WHERE  d2 >= d1 -- exclude ranges without gap right away
GROUP  BY mon
ORDER  BY mon;

• What is the difference between LATERAL and a subquery in PostgreSQL?

Postgres 9.2 or older

No LATERAL, yet. Use a subquery instead:

SELECT to_char(mon, 'YYYY') AS year
     , to_char(mon, 'MM')   AS month
     , count(*) AS ct
FROM  (
   SELECT generate_series(d1, d2, interval '1 month') AS mon
   FROM  (
      SELECT date_trunc('mon',    least(date1, date2)::timestamp) + interval '1 mon' AS d1
           , date_trunc('mon', greatest(date1, date2)::timestamp) - interval '1 mon' AS d2
      FROM   test
      ) sub1
   WHERE  d2 >= d1 -- exclude ranges without gap right away
   ) sub2
GROUP  BY mon
ORDER  BY mon;

Result

 year | month | ct
------+-------+----
 2012 |     2 |  2
 2012 |     9 |  1
 2012 |    10 |  1
 2012 |    12 |  1
 2013 |     1 |  1
 2013 |     2 |  1

db<>fiddle here

SQL Fiddle.

Explanation

You are looking for complete calendar months between the two dates.

These queries work with any dates or timestamps in ascending or descending order and should perform well.

The WHERE clause is optional, since generate_series() returns no row if start > end. But it should be a bit faster to exclude empty ranges a priori.

The cast to timestamp makes it a bit cleaner and faster. Rationale:

• Generating time series between two dates in PostgreSQL

Calculate full years and partial months between 2 dates

Take a look and see if this gives you what you are looking for.

Declare @date1 date = '11/01/2001'
Declare @date2 date = '12/01/2002'

Select @date1
    ,@date2
    ,Case 
    when datediff(M,@date1,@date2) < 12 then ''
    when datediff(M,@date1,@date2) < 24  then 'one year'
    else cast(floor(datediff(M,@date1,@date2)/12) as varchar(20)) + ' Years'
    end  +  ' ' +
Case 
    when datediff(M,@date1,@date2) - floor(datediff(M,@date1,@date2)/12)*12 = 0  then ''
    when datediff(M,@date1,@date2) - floor(datediff(M,@date1,@date2)/12)*12 = 1 then 'one month'
    else cast(datediff(M,@date1,@date2) - floor(datediff(M,@date1,@date2)/12)*12 as varchar(20)) + ' Months'
    end

---

Related Topics