Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    1

    Unanswered: DB2 Stored procedure - Add hours as input to a timestamp

    Hi!
    I want to create a stored procedure that has a IN_NROFHOURS as input, indicating number of hours. Then I want to create a cursor that fetches all rows with the following condition:

    select * from listtable where startdatetime + in_nrofhours > current timestamp

    My questions are:

    What is the best datatype for IN_NROFHOURS;
    Given the datatype you suggest, how should the "where" condition be stated?

    Thanks for your help, I appreciate that.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    You can't use just a numeric constant in the timestamp arithmetic. You must specify the units of such a constant.
    Code:
    --#SET TERMINATOR @
    
    set serveroutput on@
    
    begin atomic
      declare p_hours int default 5;
      for c1 as
        select h
        from table(values 
            current timestamp
          , current timestamp - 3 hours
          , current timestamp - 10 hours
        ) t(h)
        where h > current timestamp - p_hours hours
      do
        call dbms_output.put_line(c1.h);
      end for;
    end@
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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