Requirements
You need any version of Teradata database.
Problem Description and solution
The above stated problem can be represented as below:
When we need a literal to be added to a time stamp, it is
achieved in the following way.
Listing 1
select current_time(0),current_time(0)+interval '5' hour;
Current Time(0) (Current Time(0)+ 5)
--------------- --------------------
07:35:31+00:00 12:35:31+00:00
When the interval to be added is a variable, it cannot be
handled as a literal.
Hence, it needs to be cast as below.
Listing 2
lv_time=in_time; /* in_time is the input parameter and lv_time is the local variable */
select current_time(0),current_time(0)+cast(in_time as interval hour);
Code
Let us write a small procedure demonstrating the idea.
Listing 3
REPLACE PROCEDURE add_hour (IN in_v_hour Integer,OUT out_v_now_time time(0),OUT
out_v_add_literal_time time(0),OUT out_v_add_variable_time time(0) )
BEGIN
declare lv_time Integer; /*Declaration of a local variable to keep the interval*/
set lv_time=in_v_hour; /*Allocation of value to local variable*/
select current_time(0) into :out_v_now_time; /*Showing current time*/
select current_time+interval '5' hour into :out_v_add_literal_time; /*Showing time
modified by integer literal*/
select current_time+cast(lv_time as interval hour) into :out_v_add_variable_time;
/*Showing time modified by local variable*/
END;
Output
Listing 4
Call add_hour(5,cast(out_v_now_time as title
'now_time'),cast(out_v_add_literal_time as title
'add_literal_time'),cast(out_v_add_variable_time as title 'add_variable_time'));
*** Procedure has been executed.
*** Total elapsed time was 1 second.
now_time add_literal_time add_variable_time
-------- ---------------- -----------------
14:08:42 19:08:42 19:08:42
Explanation
An integer literal can be directly added to any time stamp
value directly where as the value held by a local variable can be added only
after casting the local variable to proper interval (hour/minute/sec).
Conclusion
By following the mentioned approach the user can add an interval
to time stamp values through local variables. This is useful where an interval
to be added is passed as an input parameter to any procedure or the interval is
to be picked from any table through a local variable.
