How to count the number of months between two dates SQL

Question

How can I count the number of times each month occurs between two dates?

eg the number of times each month occurs between 24/8/10 and 29/2/12.

you should tag the database being used as well – Vamsi Prabhala Oct 28 '15 at 16:46 — Vamsi Prabhala, Oct 28 '15 at 16:46

score 0 · Answer 1 · answered Oct 28 '15 at 16:57

0

DateDiff is the function you could use to compute the difference between 2 dates in various forms.

From the link as an example:

SELECT DATEDIFF(month, '2014/01/01', '2014/04/28');

Result: 3

answered Oct 28 '15 at 16:57

JB King

11,860
4
38
49

score 0 · Accepted Answer · answered Oct 28 '15 at 17:42

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)

How to count the number of months between two dates SQL

2 Answers2