[SQLite] Query to return Period between two Dates

[Zvoni]

I was bored silly today.......
Query returns Years, Months and Days between two Dates

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

[Zvoni]

Slightly optimized
Recognizes in ToDate a NULL or Empty String (that's not a NULL)

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