[SQLite-Tutorial] Jahre, Monate und Tage zwischen zwei Tagen

[Zvoni]

Mir war langweilig.......
Spalten:
FromDate ("VonDatum")
ToDate ("BisDatum")
Ich prüfe nicht, ob Bis grösser Von ist... Problem des Anwenders....
Ich prüfe auch nicht, ob die Felder NULL sind.... könnte man aber noch einbauen mit einem COALESCE

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 EineTablle

P.S.: Die Date-Funktion von SQLite arbeitet mit UTC. Wer local time braucht, muss Zeile 31 ändern
von:
Date(FromDate,'start of month','+1 months','-1 day')
In:
Date(FromDate,'localtime','start of month','+1 months','-1 day')

 

[Zvoni]

Verbessert mit NULL und '' (Empty String) Erkennung in ToDate.
Auch localtime jetzt drin

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

 

[Yaslaw]

Anderer Ansatz in MySQL. Errechne die differenz-Tage und mache daraus wieder ein Datum

SELECT
    t.*,
    YEAR(diff_date) AS y,
    MONTH(diff_date) AS m,
    DAY(diff_date) AS d,
    MONTH(diff_date) + YEAR(diff_date) *12 AS diff_months
FROM (
    SELECT
        t1.*,
        DATEDIFF(TO_DATE, FROM_DATE) diff_days,
        FROM_DAYS(DATEDIFF(TO_DATE, FROM_DATE)) diff_date
    FROM einetablle t1
) t

 

[Zvoni]

Hmmmm...... wenn ich das richtig verstehe, setzt du das FROM_DATE quasi im Hintergrund auf den 01. Januar 0000 und schiebst um diff_days nach vorne?
Wie ist es mit Schaltjahren? In deinem Beispiel ist kein 29. Feb. dabei, oder berücksichtigt FROM_DAYS Schalttage? Hab mich mit MySQL noch nicht wirklich auseinandergesetzt