Fast Report - SQL Lite Functions Commonly Use

From eStream Software

Useful Link

Date And Time Functions

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


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

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

Try use CAST
SELECT SUM(CAST(DocAmt AS REAL)) DocAmt FROM Main

Why on Query for Float or Currency type Field & SetDisplayFormat already set but still like not set at all?

Try use CASE WHEN... THEN... ELSE... END
SELECT CAST(CASE WHEN C1 IS NULL THEN 0.00 ELSE C1 END AS REAL) C1 FROM Main 

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

Try use strftime
 SELECT strftime(''%d/%m/%Y'', A.DocDate) AS DocDate FROM Main

How to Select with condition on Date?

Try use DATE
SELECT * FROM MAIN WHERE DOCDATE BETWEEN DATE(''2017-12-17'') AND DATE(''2017-12-31'')

How to Filter by Day?

SELECT Employee, TransDate, Amount FROM WAGES WHERE strftime(''%d'', TransDate) <= ''11''

See also