Re: SQL TIMESTAMPDIFF

Elvis,
After correcting the sql command as follows:
SELECT a.BBPCPK, a.BBPCPKD, a.BBASSIGN,  TIMESTAMPDIFF(2,  timestamp
(DIgits(a.bbcctd) || digits(a.bbcctt)) -                           
timestamp(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'                                         
I still get the same error.

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
  
 






________________________________
From: Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, November 18, 2008 1:00:24 PM
Subject: RE: SQL TIMESTAMPDIFF

Click this link:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstscal
e.htm#scatimedifstmp
and search on TIMESTAMPDIFF.

It is supported on V5R3 but you are passing arguments incorrectly.  There is
an example at that link.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: 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