No edit summary |
|||
| Line 1: | Line 1: | ||
===01. How to get the date difference results in days using SQL Lite?=== | ===01. How to get the date difference results in days using SQL Lite?=== | ||
===Example 1: Payment 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 | ||
:Date differences | ::{| class="wikitable" | ||
|- | |||
! 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 || | |||
|} | |||
<br /> | |||
{| class="mw-collapsible mw-collapsed wikitable" | {| class="mw-collapsible mw-collapsed wikitable" | ||
| Line 12: | Line 24: | ||
(julianday(Date(PaymentDate)) - julianday(Date(InvoiceDate))) | (julianday(Date(PaymentDate)) - julianday(Date(InvoiceDate))) | ||
|} | |} | ||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===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) | |||
::{| class="wikitable" | |||
|- | |||
! 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 || | |||
|} | |||
<br /> | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Julianday(Date) | |||
|- | |||
| | |||
(julianday(Date(PaymentDate))-julianday(DATE(InvoiceDate, ''start of month'', ''1 month'', ''-1 day''))) | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
Revision as of 01:56, 6 May 2016
01. 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(Date) |
|---|
|
(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(Date) |
|---|
|
(julianday(Date(PaymentDate))-julianday(DATE(InvoiceDate, start of month, 1 month, -1 day))) |