AW: SQL TIMESTAMPDIFF

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

The problem is, you do not use the SQL scalar function TIMESTAMPDIFF
documented.
According to the documentation, the scalar function TIMESTAMPDIFF has 2
parameters. The first one is an integer value representing the date/time in
which you want to return the difference. The second parameter is the
difference between 2 timestamps converted into a CHAR(22) value.

Instead of 2 parameters you use 3 parameters, the integer representing the
time difference to be returned in seconds, the second parameter is a
character value that consists of a numeric date and numeric time converted
into character and the third parameter another character value that also
consists of a numeric date and time converted into character.

Because SQL can be overloaded a Scalar Function with 3 parameters does not
exist.
Assumed bbcctd and bbcctt are numeric dates and times in both files, you
need to convert your SQL statement as follows:

SELECT a.BBPCPK, a.BBPCPKD, a.BBASSIGN, 
       TimestampDiff(2, Cast(Timestamp(VarChar(a.bbcctd) concat
VarChar(a.bbcctt)) - 
                             Timestamp(VarChar(b.bbcctd) concat
VarChar(b.bbcctt)) as Char(22))),                                        
FROM VOCTRACK a join voctrack b on          
a.bbassign= b.bbassign WHERE a.bbcctd = 20081016 and a.bbcode =     
'STR' and b.bbcode = 'END'             

Here is the decription of the scalar function TIMESTAMPDIFF from the SQL
Reference:
The TIMESTAMPDIFF function returns an estimated number of intervals of the
type defined by the first argument, based on the difference between two
timestamps. 
--> TIMESTAMPDIFF ( numeric-expression , string-expression ) 

numeric-expression: The first argument must be a built-in data type of
either INTEGER or SMALLINT.
string-expression: string-expression is the result of subtracting two
timestamps and converting the result to a string of length 22. 
    The argument must be an expression that returns a value of a built-in
character string or a graphic string. 
    If string-expression is a character or graphic string, it must not be a
CLOB or DBCLOB.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Young
Gesendet: Tuesday, 18. November 2008 18:31
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL TIMESTAMPDIFF

According the the V5R3 SQL Reference manual, this built in function should
return the difference between 2 timestamps.  When I attempt to execute a
statement with it, I get the error
Message ID . . . . . . :  
SQL0204                                             
                                                                            
   
                                                                            
   
Message . . . . :   TIMESTAMPDIFF in *LIBL type *N not
found.                  
                                                                            
   
Cause . . . . . :   TIMESTAMPDIFF in *LIBL type *N was not found. If this
is   
  an ALTER TABLE statement and the type is *N, a constraint or partition
was   
  not found. If this is not an ALTER TABLE statement and the type is *N,
a     
  function, procedure, or trigger was not
found.                               
    If a function was not found, TIMESTAMPDIFF is the service program
that     
  contains the function.  The function will not be found unless the
external   
  name and usage name do not match exactly.  Examine the job log for a
message 
  that gives more details on which function name is being searched for and
the 
  name that did not
match.                                                     
 
The SQL command I am attempting to build is :
SELECT a.BBPCPK, a.BBPCPKD, a.BBASSIGN, TIMESTAMPDIFF(2,            
(DIgits(a.bbcctd) || digits(a.bbcctt)), (DIgits(b.bbcctd) ||        
digits(b.bbcctt))) diff FROM VOCTRACK a join voctrack b on          
a.bbassign= b.bbassign WHERE a.bbcctd = 20081016 and a.bbcode =     
'STR' and b.bbcode = 'END'             

The concatanation of variables for parameter 2 and 3 do result in valid
timestamp values.

Was this BIF introduced later as a PTF?

The system I am using is running V5R3, but is way behind in PTF's.
I have no control over the ordering of PFT's or upgrading to a later
release.

Thanks,
                             
Jeff Young 
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3