[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
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