...

10.2 Calculate number of days between two dates

 
spl-cookbook(224)

Find the number of days between two date/datetime values.
Find orders that are delivered at least 30 days after the order is placed in the year 2015. Below is Orders table:

ID CustomerID OrderDate DeliveryDate Amount
10248 VINET 2012/07/04 2012/07/16 428.0
10249 TOMSP 2012/07/05 2012/07/10 1842.0
10250 HANAR 2012/07/08 2012/07/12 1523.5
10251 VICTE 2012/07/08 2012/07/15 624.95
10252 SUPRD 2012/07/09 2012/07/11 3559.5
… … … … …

SPL uses operator “-” to calculate the number of days between two date/datetime values. SPL script is as follows:

A
1 =connect(“db”)
2 =A1.query@x(“select * from Orders”)
3 =A2.select(year(OrderDate)==2014 && DeliveryDate-OrderDate>30)

A1 Connect to the database.
A2 Import Orders table.
A3 Use operator “-” to calculate the number of days between the delivery date and the order date.

Execution result:

ID CustomerID OrderDate DeliveryDate Amount
10924 BERGS 2014/03/04 2014/04/08 1835.7
10927 LACOR 2014/03/05 2014/04/08 800.0
10970 BOLID 2014/03/24 2014/04/24 224

 

spl-cookbook(224)
 
 •  189 View  •