[SQLite] Calculate Difference in full months between two Dates

[Zvoni]

Searching the Internet revealed that to be a common problem
Since SQLite lacks a Function like PERIOD_DIFF in MySQL, and i needed that information for one of my queries, i wrote my own.

Requires: A Table called "Test" (or change table and column-names to suit you) and the two DateColumns

Description:
To get the difference in full months you have to move both Dates to the start of month each.
e.g.
2022-03-18 to 2022-05-10 are three full months (March, April, May)

adjusted to
2022-03-01 to 2022-05-01, because that way you avoid the issue of February and Leap-Years

SELECT COUNT() AS DIFFMONTHS FROM 
(WITH RECURSIVE cnt(FromDate, ToDate) AS
    (SELECT Date(FromDate,'start of month') As 'FromDate', coalesce(Date(ToDate,'start of month'),Date('Now','localtime','start of month')) As 'ToDate' 
     FROM test
     UNION ALL
     SELECT Date(FromDate,'+1 months') As 'FromDate', coalesce(Date(ToDate,'start of month'),Date('Now','localtime','start of month')) As 'ToDate' 
     FROM cnt
     WHERE Date(FromDate,'+1 months')<=coalesce(Date(ToDate,'start of month'),Date('Now','localtime','start of month'))
     )
SELECT Date(FromDate,'start of month') As 'FromDate', coalesce(Date(ToDate,'start of month'),Date('Now','localtime','start of month')) As 'ToDate' FROM cnt)

Above SQL could be adjusted to other DBMS not having native functions.
Needs to support recursive CTE though

[wqweto]

Btw, can try posting a link to complete working solution on https://dbfiddle.uk/?rdbms=sqlite_3.27

cheers,
</wqw>

[Zvoni]

Btw, can try posting a link to complete working solution on https://dbfiddle.uk/?rdbms=sqlite_3.27

cheers,
</wqw>

Could you try it?
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=6e0c2df33feafe222315d7864796f5d1