Re: Converting zoned field to a TIME value in SQL

if you do this a lot consider the following:

create function routines/dectodate
(timedec dec (6,0))
returns time
language sql
returns null on null input
begin
declare timedigits char (6);
declare timesep char(8);
declare returntime time;
set timedigits = digits(timedec);
set timesep = substr(timedigits,1,2) concat ':' concat
substr(timedigits,3,2) concat ':' concat
substr(timedigits,5,2);
set returntime = time(timesep);
return returntime;
end


Then you could do
select bstime, routines.dectodate(bstime)
from qtemp.testtime

And, if you want to do that in Query/400 you'd simply do a:
create view qtemp.testtimel1 as (
select bstime, routines.dectodate(bstime) as realtime
from qtemp.testtime)

Then they could Query/400 against the "logical file" testtimel1.

Understand?


Rob Berendt