LogoASPAlliance: Articles, reviews, and samples for .NET Developers
CodeSnip: How to Add an Interval to a Time Stamp Field Being Selected Through a Local Variable
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 14224/ 54


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);


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) )
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*/


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


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).


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.

©Copyright 1998-2020  |  Page Processed at 2020-07-16 1:42:15 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search