As SQL has been mentioned as a possible solution, it should be noted
that the TIMESTAMPDIFF function isn't great (it's almost useless ?).
The following is from the SQL Reference: -
The following assumptions may be used in estimating the difference:
there are 365 days in a year
there are 30 days in a month
there are 24 hours in a day
there are 60 minutes in an hour
there are 60 seconds in a minute
These assumptions are used when converting the information in the second
argument, which is a timestamp duration, to the interval type specified
in the first argument. The returned estimate may vary by a number of
days. For example, if the number of days (interval 16) is requested for
a difference in timestamps for '1997-03-01-00.00.00' and
'1997-02-01-00.00.00', the result is 30. This is because the difference
between the timestamps is 1 month so the assumption of 30 days in a
month applies.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: 20 November 2008 01:27
To: RPG programming on the AS400 / iSeries
Subject: Re: Create TimeStamp from numeric fields
Hi Jeff,
have a record containing 2 fields containing a numeric representation
of a date (8,2) and 2 associated fields containing a numeric
representation of an associated time (6,0).fields.
As so many others have said, I don't understand the 8,2 date. I don't
know what format that is? Is it YYYYMM.DD? Or YYMMDD.CC? Or, what is
it?
Bearing in mind that I'm not sure how that date format works, I'll give
a sample with YYYYMM.DD dates -- and if that's not right, you might have
to modified this code accordingly:
D ts1 s Z
D ts2 s Z
D diff s 10i 0
/free
ts1 = %date( %dec(date1*100:8:0) : *ISO )
+ %time(time1:*HMS);
ts2 = %date( %dec(date2*100:8:0) : *ISO )
+ %time(time2:*HMS);
diff = %diff(ts1: ts2: *seconds);
The only thing that was even slightly tricky is that the first parameter
to %DATE() has to be a numeric field with no decimal places (or a
character field...) So I simply multiplied it by 100, and used %DEC()
to make the intermediate result be an 8,0 field. From there, the rest
was easy. Fairly basic %DATE() + %TIME() = %TIMESTAMP() logic.
Not sure why you brought up SQL. This is actually easier to do in RPG
than it would be in SQL (though, SQL is certainly capable, it'd require
a bit more work...)
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
