Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Unanswered: how to update current date and current time in DB2 table

    Hi Experts,

    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.

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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

  3. #3
    Join Date
    Mar 2015
    Posts
    3
    yet not tried, I hope that will work.
    Thank you.
    I am thinking to use the below query, i hope it will work.

    UPDATE schema.xyz SET ProcessedTime=
    (SELECT HOUR (CURRENT TIME) * 10000 + MINUTE (CURRENT TIME) * 100 + SECOND (CURRENT TIME) FROM
    schema.xyz WHERE ProcessedTime= 0 FETCH FIRST 1 ROWS ONLY) WHERE ProcessedTime= 0

    After testing will update the thread

    Regards,
    Akhila

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    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
    Regards,
    Mark.

  5. #5
    Join Date
    Mar 2015
    Posts
    3
    Thank you Mark.
    Will try and let you know.

    Regards,
    Akhila

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •