Results 1 to 3 of 3

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

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    [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
    Last edited by Zvoni; Mar 24th, 2022 at 05:57 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2

  3. #3

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

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

    Quote Originally Posted by wqweto View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width