# Thread: [SQLite] Calculate Difference in full months between two Dates

1. ## [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)

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

2. ## 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>

3. ## Re: [SQLite] Calculate Difference in full months between two Dates

Originally Posted by wqweto
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•