Results 1 to 2 of 2

Thread: [SQLite] Query to return Period between two Dates

  1. #1

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

    [SQLite] Query to return Period between two Dates

    I was bored silly today.......
    Query returns Years, Months and Days between two Dates
    Code:
    SELECT 
    (CASE CAST(strftime('%m',ToDate) AS INT)<CAST(strftime('%m',FromDate) AS INT) OR 
         (CAST(strftime('%m',ToDate) AS INT)=CAST(strftime('%m',FromDate) AS INT) AND 
          CAST(strftime('%d',ToDate) AS INT)<CAST(strftime('%d',FromDate) AS INT))
        WHEN TRUE 
        THEN 
            CAST(strftime('%Y',ToDate) AS INT)-CAST(strftime('%Y',FromDate) AS INT)-1
        ELSE 
            CAST(strftime('%Y',ToDate) AS INT)-CAST(strftime('%Y',FromDate) AS INT)
    END) AS Years,
    (CASE WHEN CAST(strftime('%m',ToDate) AS INT)<CAST(strftime('%m',FromDate) AS INT)      
        THEN     
            CASE WHEN CAST(strftime('%d',ToDate) AS INT)<CAST(strftime('%d',FromDate) AS INT)     
                THEN CASE WHEN 12+CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)=0 
                        THEN 11 
                        ELSE 11+CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)
                     END            
                ELSE 12+CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)
            END 
        ELSE 
            CASE WHEN CAST(strftime('%d',ToDate) AS INT)<CAST(strftime('%d',FromDate) AS INT)     
                THEN CASE WHEN CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)=0 
                        THEN 11 
                        ELSE CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)-1 
                     END            
                ELSE CAST(strftime('%m',ToDate) AS INT)-CAST(strftime('%m',FromDate) AS INT)
            END 
    END) AS Months ,
    (CASE WHEN CAST(strftime('%d',ToDate) AS INT)>=CAST(strftime('%d',FromDate) AS INT)     
        THEN CAST(strftime('%d',ToDate) AS INT)-CAST(strftime('%d',FromDate) AS INT) 
        ELSE CAST(strftime('%d',ToDate) AS INT)+(CAST(strftime('%d',Date(FromDate,'start of month','+1 months','-1 day')) AS INT)-CAST(strftime('%d',FromDate) AS INT)) 
    END) AS Days 
    FROM Test
    Try it here: https://dbfiddle.uk/?rdbms=sqlite_3....4b9b06fa2851d3
    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

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

    Re: [SQLite] Query to return Period between two Dates

    Slightly optimized
    Recognizes in ToDate a NULL or Empty String (that's not a NULL)
    Code:
    SELECT 
    (CASE CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)<CAST(strftime('%m',FromDate) AS INT) OR 
         (CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)=CAST(strftime('%m',FromDate) AS INT) AND 
          CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)<CAST(strftime('%d',FromDate) AS INT))
        WHEN TRUE 
        THEN 
            CAST(strftime('%Y',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%Y',FromDate) AS INT)-1
        ELSE 
            CAST(strftime('%Y',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%Y',FromDate) AS INT)
    END) AS Years,
    (CASE WHEN CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)<CAST(strftime('%m',FromDate) AS INT)      
        THEN     
            CASE WHEN CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)<CAST(strftime('%d',FromDate) AS INT)     
                THEN CASE WHEN 12+CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)=0 
                        THEN 11 
                        ELSE 11+CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)
                     END            
                ELSE 12+CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)
            END 
        ELSE 
            CASE WHEN CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)<CAST(strftime('%d',FromDate) AS INT)     
                THEN CASE WHEN CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)=0 
                        THEN 11 
                        ELSE CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)-1 
                     END            
                ELSE CAST(strftime('%m',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%m',FromDate) AS INT)
            END 
    END) AS Months ,
    (CASE WHEN CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)>=CAST(strftime('%d',FromDate) AS INT)     
        THEN CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)-CAST(strftime('%d',FromDate) AS INT) 
        ELSE CAST(strftime('%d',COALESCE(CASE WHEN Trim(ToDate)='' THEN NULL ELSE ToDate END, Date('now','localtime'))) AS INT)+(CAST(strftime('%d',Date(FromDate,'localtime','start of month','+1 months','-1 day')) AS INT)-CAST(strftime('%d',FromDate) AS INT)) 
    END) AS Days 
    FROM Test
    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