CodeSnip: How to Add an Interval to a Time Stamp Field Being Selected Through a Local Variable
page 1 of 1
Published: 10 Nov 2008
The Teradata syntax does not allow any time interval (i.e. Hr/Min/Sec) to be selected through a local variable to be added to a Timestamp value. It only permits the exact interval literal being put through a pair of quotation marks. This creates some serious limitations where the interval to be added is used through a local variable for manipulation. The aim of this Code Snippet is to provide a workaround for the use of local variables. Deepankar provides a short description of the problem followed by the suggested solution with the help of the source code. He also provides a snapshot of the final output with relevant analysis.
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 12162/ 26


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.

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2017  |  Page Processed at 2017-01-21 8:14:26 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search