The query below calculates number of days between two dates.
For example:
- The user needs to calculate the difference between today and the
"Delivery date" in an order document.
- The user needs to display the delay in days, according to the display in open Items list => open invoices.
- Create the query below and name it "delay in days":
/* begin */
select DocNum,DocDueDate,
CASE WHEN DATEDIFF(day, DocDueDate,getdate()) >= 0 THEN
DATEDIFF(day, DocDueDate,getdate())
ELSE 0
END AS Delta
from ORDR
/* end */
In the "Delta" column you will see the delay in days. You can add a condition at the end of the query (after the "from ORDR") as follows:
/*begin*/
WHERE DATEDIFF(day, DocDueDate,getdate()) >= 0
/*end*/
If the Delivery date is later than today and there is no delay yet the "Delta" column will be empty. In this case if you wish to display the number of the remaining days you will get the result with a minus sign (-). If you want to do so replace the above query with the query below:
/* begin*/
select DocNum,DocDueDate, DATEDIFF(day, DocDueDate,getdate())AS 'Delta'
from ORDR
/*end*/