format time in query

[lespaul]

Is there any way to format the timestamp datatype into a normal time value? I have a time field that shows:
[tt]
10:08:36 AM[/tt]

I'd like to display

[tt]
10:08 AM[/tt]

I have tried substring and parsing the time apart, but when you convert to a string the : is replace with . and I lose the AM. So now it's starting to get convoluted trying to get the AM or PM....

SELECT intvwid, intvwdate, casepre, casenum, intvwtime, char(intvwtime), substring(char(intvwtime), 1, position('.' in char(intvwtime))-1) ||':'|| substring(char(intvwtime), position('.' in char(intvwtime)) +1, 2)

I found a function on the IBM site (

http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html)

that was suppose to do it but it's not correct...I get syntax errors when trying to run it on the iSeries....

create function ts_fmt(TS timestamp, fmt varchar(20)) 
returns varchar(50) 
return 
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as 
( 
    select 
    substr( digits (day(TS)),9), 
    substr( digits (month(TS)),9) , 
    rtrim(char(year(TS))) , 
    substr( digits (hour(TS)),9), 
    substr( digits (minute(TS)),9), 
    substr( digits (second(TS)),9), 
    rtrim(char(microsecond(TS))) 
    from sysibm.sysdummy1 
    ) 
select 
case fmt 
    when 'yyyymmdd' 
        then yyyy || mm || dd 
    when 'mm/dd/yyyy' 
        then mm || '/' || dd || '/' || yyyy 
    when 'yyyy/dd/mm hh:mi:ss' 
        then yyyy || '/' || mm || '/' || dd || ' ' ||  
               hh || ':' || mi || ':' || ss 
    when 'nnnnnn' 
        then nnnnnn 
    else 
        'date format ' || coalesce(fmt,' <null> ') ||  
        ' not recognized.' 
    end 
from tmp

when I run the syntax check on this I get the error:
[tt]Keyword RETURN not expected. Valid tokens: IS NO NOT NULL ALLOW FINAL READS CALLED DBINFO FENCED SOURCE. Cause . . . . . : The keyword RETURN was not expected here. A syntax error was detected at keyword RETURN.[/tt]

Any suggestions for getting the time in the correct format?

thanks!

Leslie

Have you met Hardy Heron?

 

[Mercury2]

1/ Set option TIMFMT=*USA

2/ Select TIME(MyTimestamp) from ...

 

[lespaul]

ok where would I set that? I'm running this from an external program specifically a web app and I'm trying to do this in my query or a function...thanks!

Leslie

Have you met Hardy Heron?

 

[Mercury2]

I think that your best bet is to create a function as below

CREATE FUNCTION AMPMTIME ( MYTIMESTAMP TIMESTAMP )  
 RETURNS CHAR( 10 )                                   
 LANGUAGE SQL                                       
 DETERMINISTIC                                      
 SET OPTION TIMFMT = *USA                           
                                                    
BEGIN                                               
   DECLARE USATIME CHAR( 10 );                        
   SET USATIME = TIME( MYTIMESTAMP );                 
   CALL SNDPGMMSGP ( USATIME );                     
   RETURN USATIME;                                  
END

Then use the new created function on your SELECT stm

Select AMPMTIME( MyTimestamp ) from ...

 

[Mercury2]

You cannot update your posts here, I hate this !

So, pls take out the line

CALL SNDPGMMSGP ( USATIME );

which is there for my own debug purposes only.