Fast Report - SQL Lite Functions Commonly Use

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

[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

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

[top]

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 

[top]

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

[top]

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

[top]

How to Filter by Day?

Below example is filter below and equal 11th

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

[top]

How to Filter by Data with NULL?

Below example is filter ItemCode not equal to RTN5Cents

SELECT * FROM Document_Detail WHERE (COALESCE(ITEMCODE, '''','''')) <> ''RTN5Cents'''

[top]

How to Top 5 rows Data?

SELECT * FROM Main Order By DocAmt Desc Limit 5

[top]

See also

• Report Designer
• Others Customisation