Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] timestamp arithmetic.
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] timestamp arithmetic.
 Login/Join
 
Master
posted
I have a master file with two fields
FIELDNAME=CLOCK_IN, ALIAS=CLOCK_IN, USAGE=HYYMDm, ACTUAL=HYYMDm,
MISSING=ON, $

FIELDNAME=CLOCK_OUT, ALIAS=CLOCK_OUT, USAGE=HYYMDm, ACTUAL=HYYMDm,
MISSING=ON, $

both are DB2 Timestamp fields. Is there any way to subtract clock_out - clock_in? When I create a compute just like that, I end up with a can't do math on an alphanumeric error.

Searching IBI's website shows a lot of how to convert data into timestamps, but not how to do arithmetic with them.

Thanks!

This message has been edited. Last edited by: Kerry,


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Well hopefully there is an easier way.

http://techsupport.informationbuilders.com/sps/12162011.html


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Jason, give the HDIFF function a try:
quote:
Syntax: How to Find the Number of Units Between Two Date-Time Values
HDIFF(value1, value2, 'component', outfield) where:

value1 - Date-time - Is the end date-time value, the name of a date-time field that contains the value, or an expression that returns the value.

value2 - Date-time - Is the start date-time value, the name of a date-time field that contains the value, or an expression that returns the value.

component - Alphanumeric - Is the name of the component to be used in the calculation enclosed in single quotation marks. If the component is a week, the WEEKFIRST parameter setting is used in the calculation.

outfield - Floating-point double-precision -Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The format must be floating-point double-precision.

E.G.:
CONF_DIFF/D12 = HDIFF(DT_ADLSCONF, DT_ADLSINIT, 'SECOND', CONF_DIFF);
DL_AGE/D8     = HDIFF(EFFECTIVEDATE,DATEOFBIRTH,'YEAR','D8');
MINUTES/D20   = HDIFF(COMPLETION_DATE,CREATION_DATE,'MINUTE','D20') ;

-SET &MILSEC  = HDIFF(&ENDTIME, &BEGTIME, 'MILLISECOND', D12.2);


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
great info!
thanks!


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Is there a singe function to show the results of HDIFF or DATEDIF in a format such as DD HH:MM?

as follws:
A) (8/1/2008 4:58:00 PM) - (Aug 29, 08 2:08 PM) = 0 01:26 ... 0 days, 1 hour 26 minutes

B) (8/1/2008 4:58:00 PM) - (8/5/2008 6:24 PM) = 4 01:26 ... 4 days, 1 hour 26 minutes

[WF 764] Thanks in advance - Doug


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
 
Posts: 3044 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
<JG>
posted
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] timestamp arithmetic.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.