No edit summary |
|||
(21 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
=== | ==Useful Link== | ||
[https://www.sqlite.org/lang_datefunc.html 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 === | ===Example 1: Date different in between the Payment Date and Invoice Date === | ||
:Payment Date = 05/05/2016 | :Payment Date = 05/05/2016 | ||
:Invoice Date = 15/03/2016 | :Invoice Date = 15/03/2016 | ||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |- | ||
! Month !! Days !! | ! Month !! Days !! | ||
Line 18: | Line 20: | ||
<br /> | <br /> | ||
{| class=" | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
! Julianday( | ! Julianday(Date2) - Julianday(Date1) | ||
|- | |- | ||
| | | | ||
Line 25: | Line 27: | ||
|} | |} | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
===Example 2: Date different in between the Payment Date and the month end of the Invoice Date === | ===Example 2: Date different in between the Payment Date and the month end of the Invoice Date === | ||
Line 31: | Line 32: | ||
:Invoice Date = 15/03/2016 (31/03/2016) | :Invoice Date = 15/03/2016 (31/03/2016) | ||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |- | ||
! Month !! Days !! | ! Month !! Days !! | ||
Line 43: | Line 44: | ||
<br /> | <br /> | ||
{| class=" | {| class="wikitable" style="margin: 1em auto 1em auto;" | ||
! Julianday( | ! Julianday(Date2) - Julianday(Date1, ''start of month'', ''1 month'',''-1 day'')) | ||
|- | |- | ||
| | | | ||
Line 50: | Line 51: | ||
|} | |} | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
==Why on Query for Float or Currency type Field (eg DocAmt) the result become Integer?== | |||
: Try use ''CAST'' | |||
<pre>SELECT SUM(CAST(DocAmt AS REAL)) DocAmt FROM Main</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==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'' | |||
<pre>SELECT CAST(CASE WHEN C1 IS NULL THEN 0.00 ELSE C1 END AS REAL) C1 FROM Main </pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==Why on Query for Date type field (eg DocDate) the date is in yyyymmdd format?== | |||
: Try use ''strftime'' | |||
<pre> SELECT strftime(''%d/%m/%Y'', A.DocDate) AS DocDate FROM Main</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==How to Select with condition on Date?== | |||
: Try use ''DATE'' | |||
<pre>SELECT * FROM MAIN WHERE DOCDATE BETWEEN DATE(''2017-12-17'') AND DATE(''2017-12-31'')</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==How to Filter by Day?== | |||
: Below example is filter below and equal 11th | |||
<pre>SELECT Employee, TransDate, Amount FROM WAGES WHERE strftime(''%d'', TransDate) <= ''11''</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==How to Filter by Data with NULL?== | |||
: Below example is filter ItemCode not equal to RTN5Cents | |||
<pre>SELECT * FROM Document_Detail WHERE (COALESCE(ITEMCODE, '''','''')) <> ''RTN5Cents'''</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==How to Top 5 rows Data?== | |||
: Try use ''Limit'' | |||
<pre>SELECT * FROM Main Order By DocAmt Desc Limit 5</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==See also== | |||
* [[Report Designer]] | |||
* Others [[Customisation]] |
Latest revision as of 06:31, 21 June 2023
Useful Link
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?
- Try use Limit
SELECT * FROM Main Order By DocAmt Desc Limit 5
See also
- Report Designer
- Others Customisation