Unanswered: how to update current date and current time in DB2 table
I am new to SQL, trying to update the table with processed date (current date) and time(current time) in DB2.
I am picking up the data and updating two fields processedDate (8, YYYYMMDD) and processed time(6, HHMMSS).
Below is the query to pick the data from DB2 table:
SELECT columns FROM schema.table WHERE ProcessedDate= 0 AND processedTime= 0 ORDER BY column (orderNumber)
Update the processedDate:
UPDATE schema.xyz SET ProcessedDate=
(SELECT YEAR (CURRENT DATE) * 10000 + MONTH(CURRENT DATE) * 100 + DAY (CURRENT DATE) FROM
schema.xyz WHERE ProcessedDate= 0 FETCH FIRST 1 ROWS ONLY) WHERE ProcessedDate= 0
I hope the above will work for processedDate (current date).
how do we update the processedTime in the same table?
Need you suggestions.
if processeddate is something like bigint, it should work - have you tried ?
similar for time functions hour(current time) minutes(current time)
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
UPDATE schema.xyz SET
ProcessedDate=case ProcessedDate when 0 then int(current date) else ProcessedDate end
, ProcessedTime=case ProcessedTime when 0 then int(current time) else ProcessedTime end
WHERE ProcessedDate=0 or ProcessedTime=0