[SQLite] Calculate Difference in full months between two Dates
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
Code:
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
Re: [SQLite] Calculate Difference in full months between two Dates
Btw, can try posting a link to complete working solution on https://dbfiddle.uk/?rdbms=sqlite_3.27
cheers,
</wqw>
Re: [SQLite] Calculate Difference in full months between two Dates
