RE: Help with time stamps in SQL

update myLib/myTable set (lastchgdat,lastchgtim,data) =            
      (decimal(substr(char(current_date,ISO),1,4) ||          
               substr(char(current_date,ISO),6,2) ||          
               substr(char(current_date,ISO),9,2)) - 19000000,
       decimal(substr(char(current_time),1,2) ||              
               substr(char(current_time),4,2) ||              
               substr(char(current_time),7,2)),               
       'mydata')                                              

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
Subject: Help with time stamps in SQL

I have a number of mass database updates where SQL would be very convenient
instead of writing RPG programs. 
 
The complication is that my UPDATE statement also needs to populate a couple
of audit fields: a 7 digit numeric field that holds the Last Change Date
(CYYMMDD format) and a 6 digit numeric field for the Last Change Time
(HHMMSS format).
 
For example, a record updated on Jan 20, 2007 at 11:30:43AM should be
updated with Change Date = 1070120 and Change Time = 113043 (since we are
already in the third millennium, I don't need to be concerned with the zero
century digit for dates earlier than 2000).
 
Is there a way to retrieve the current date and time into my UPDATE
statement and format them to obtain the time stamp fields that I need?
 
Thank you!