timestampdiff on V5R3

[gcraill]

According to here, and here (printed page number 333 or actual page 352) this should work ... any hints as to what's not flying for me?

[B]select timestampdiff( 8, current_timestamp-SNPSHT ) as fish  
from jrnrcvnow                                           [/B]
    
[COLOR=#ff0000]TIMESTAMPDIFF in *LIBL type *N not found.  [/COLOR]

[B.Hauser]

Re: timestampdiff on V5R3

The Second Parameter must be CHAR(22) representing the timestamp difference without separators and NOT a numeric value!
Because SQL functions can be overloaded, a function TIMESTAMPDIFF with an Integer and a numeric parameter is searched which does not exist.
That's why the function is not found.

select timestampdiff( 8, Cast(current_timestamp-SNPSHT as Char(22)) as fish  
from jrnrcvnow

Birgitta

[gcraill]

Re: timestampdiff on V5R3

RTFM!

select 
cast((timestampdiff(2,cast(current_timestamp-SNPSHT as char(22)))/3600) as dec(3,2)) as frog                                          
from jrnrcvnow

It is correctly (for my data) giving me 1.00 as it is over 1 hour difference.

.... even to go a bit further ...

select cast( real( timestampdiff ( 2,           
  cast( current_timestamp - SNPSHT as char(22) )  
  ) ) / 3600 as dec(5,2) ) as HrsDiff           
from jrnrcvnow

Gives me the correct figure of 1.32 Hours (at time of run)

[gcraill]

Re: timestampdiff on V5R3

Thanks Birgitta - I had actually figured that out, but went to lunch before posting

.... But I didn't know about the overloading lark, I struggle with half loading myself, no need to go too far on these things!