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')
See also
- Report Designer
- Others Customisation