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?

Below example is filter below and equal 11th
SELECT Employee, TransDate, Amount FROM WAGES WHERE strftime(''%d'', TransDate) <= ''11''

How to Filter by Data with NULL?

Below example is filter ItemCode not equal to RTN5Cents
SELECT * FROM Document_Detail WHERE (COALESCE(ITEMCODE, '''','''')) <> ''RTN5Cents'''

How to Top 5 rows Data?

SELECT * FROM Main Order By DocAmt Desc Limit 5

See also