Re: SQL TIMESTAMPDIFF

Adam,
The problem I am having is that the system is not finding the TIMESTAMPDIFF built in function in SQL.

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: Adam Glauser <adamglauser@xxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Tuesday, November 18, 2008 12:46:23 PM
Subject: Re: SQL TIMESTAMPDIFF

Jeff Young wrote:

According the the V5R3 SQL Reference manual, this built in function should return the difference between 2 timestamps.
[...]
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.

Instead of TIMSTAMPDIFF(diffUnits, timestamp1, timestamp2), I think you
want TIMSTAMPDIFF(diffUnits, timestamp1 - timestamp2).

The way I read the docs, TIMESTAMPDIFF converts the duration string
which is the result of timestamp subtraction into a single unit, it
doesn't actually calculate the difference.