Thursday, December 1, 2016

Difference Between Two Dates

How do I find the difference between two dates

While on training recently I was asked by a customer to find the difference between a due-date for a payment and the current date. However, they only wanted to list the dates that were overdue as the number of days overdue, and if the payment was not overdue then they should show a zero as the days difference.

Now the best way to calculate a difference between two days is to convert the date using the $days function and subtract the two dates.

The $days function converts any date to the number of days since 4713 BC, and is known as Julian Day number. Therefore you can perform or figure out the difference between two dates. The task below has a couple of tricks, the first is to use Suprtool to build a command file to get the current date into a variable in Julian Day format!

The second part of the script figures out the difference between the two dates, keep in mind that the customer wants to know which dates are "overdue" and only want to see those that are overdue, so we re-order the subtraction such that overdue payments will be a positive number and those payments that are not yet due, will be a negative number.

rm tdays tdays.sd
rm setdate
export EQ_DBSERVER=":8202"
suprtool << EOD_Suprtool
{ Set $mydate variable to todays date in julian day format }
base membrs,5,lookup
get member-file
num 1
def tdays,1,4,double
item tdays,date,julian
ext tdays=\$today
out tdays,link
xeq
in tdays
def tdayascii,1,7,byte
ext 'export MYJULDAY="'
ext tdayascii=\$edit(tdays,"zzzzzzz")
ext '"'
out setdate
exit
EOD_Suprtool
chmod +x setdate
. ./setdate
rm setdate
suprtool << EOD_Suprtool
set varsub on
in dates
def diff,1,4,double
item a,date,ccyymmdd
ext a
ext diff=(\$MYJULDAY - \$DAYS(A))
list
xeq
exit
EOD_Suprtool
Given some dates here is what you would see:
 >IN dates (0) >OUT $NULL (0)
A               = 20131213       DIFF            = 47

>IN dates (1) >OUT $NULL (1)
A               = 20131231       DIFF            = 29

>IN dates (2) >OUT $NULL (2)
A               = 20140201       DIFF            = -3

>IN dates (3) >OUT $NULL (3)
A               = 20140130       DIFF            = -1

>IN dates (4) >OUT $NULL (4)
A               = 20140115       DIFF            = 14

IN=5, OUT=5. CPU-Sec=1. Wall-Sec=1.

>exit

Now since we only want to see positive numbers, if we change the target type to be logical, we will only get the difference in days with a positive number and the negatives will become zero since the definition of a logical number is that it cannot be negative.
 Neo%dev/source/suprtool: ./datediff
rm: setdate non-existent
SUPRTOOL/UXia/Copyright Robelle Solutions Technology Inc. 1981-2014.
(Version 5.6 Internal)  WED, JAN 29, 2014, 11:55 AM  Type H for help.
Build 11
>{ Set  variable to todays date in julian day format }
>base membrs,5,lookup
>get member-file
>num 1
>def tdays,1,4,double
>item tdays,date,julian
>ext tdays=$today
>out tdays,link
>xeq

Warning:  NUMRECS exceeded; some records not processed.
IN=2, OUT=1. CPU-Sec=1. Wall-Sec=1.

>in tdays
>def tdayascii,1,7,byte
>ext 'export MYJULDAY="'
>ext tdayascii=$edit(tdays,"zzzzzzz")
>ext '"'
>out setdate
>exit
IN=1, OUT=1. CPU-Sec=1. Wall-Sec=1.

SUPRTOOL/UXia/Copyright Robelle Solutions Technology Inc. 1981-2014.
(Version 5.6 Internal)  WED, JAN 29, 2014, 11:55 AM  Type H for help.
Build 11
>set varsub on
>in dates
>def diff,1,4,logical  {note target is now logical}
>item a,date,ccyymmdd
>ext a
>ext diff=($MYJULDAY - $DAYS(A))
>list
>xeq
>IN dates (0) >OUT $NULL (0)
A               = 20131213       DIFF            = 47

>IN dates (1) >OUT $NULL (1)
A               = 20131231       DIFF            = 29

>IN dates (2) >OUT $NULL (2)
A               = 20140201       DIFF            = 0

>IN dates (3) >OUT $NULL (3)
A               = 20140130       DIFF            = 0

>IN dates (4) >OUT $NULL (4)
A               = 20140115       DIFF            = 14

IN=5, OUT=5. CPU-Sec=1. Wall-Sec=1.

>exit

No comments:

Post a Comment