Fast Report - SQL Lite Functions Commonly Use

How to get the date difference results in days using SQL Lite?

Example 1: Date different in between the Payment Date and Invoice Date

Payment Date = 05/05/2016

Invoice Date = 15/03/2016

Month	Days
Mar 2016	16	Date from Invoice Date, ie. 16/03/2016
April 2016	30	April month, ie. 01/04/2016 - 30/04/2016
May 2016	5	Date to payment date, ie. 05/05/2016
Date differences	51

Julianday(Date2) - Julianday(Date1)
(julianday(Date(PaymentDate)) - julianday(Date(InvoiceDate)))

[top]

Example 2: Date different in between the Payment Date and the month end of the Invoice Date

Payment Date = 05/05/2016

Invoice Date = 15/03/2016 (31/03/2016)

Month	Days
April 2016	30	Date from Invoice Date, ie. 01/04/2016
May 2016	5	Date to payment date, ie. 05/05/2016
Date differences	35

Julianday(Date2) - Julianday(Date1, start of month, 1 month,-1 day))
(julianday(Date(PaymentDate))-julianday(DATE(InvoiceDate, start of month, 1 month, -1 day)))

[top]

Why on Query for Float or Currency type Field (eg DocAmt) the result become Integer?

Try use CAST (eg SUM(CAST(DocAmt AS REAL)) DocAmt....)

[top]

Why after Query for Date type field (eg DocDate) the date look in yyyymmdd format?

Try use strftime (eg strftime(%d/%m/%Y, A.DocDate) AS DocDate )

[top]

See also

• Report Designer
• Others Customisation