How to add/subtract date/time components using a calculated interval?

Question

I'd like to get this to work in Teradata:

Updated SQL for better example

select
case
    when 
        current_date between
        cast('03-10-2013' as date format 'mm-dd-yyyy') and
        cast('11-03-2013' as date format 'mm-dd-yyyy')
    then 4
    else 5
end Offset,
(current_timestamp + interval Offset hour) GMT

However, I get an error of Expected something like a string or a Unicode character blah blah. It seems that you have to hardcode the interval like this:

select current_timestamp + interval '4' day

Yes, I know I hardcoded it in my first example, but that was only to demonstrate a calculated result.

If you must know, I am having to convert all dates and times in a few tables to GMT, but I have to account for daylight savings time. I am in Eastern, so I need to add 4 hours if the date is within the DST timeframe and add 5 hours otherwise.

I know I can just create separate update statements for each period and just change the value from a 4 to a 5 accordingly, but I want my query to be dynamic and smart.

score 5 · Accepted Answer · answered Jun 14 '13 at 12:01

Here's the solution:

select
case
    when 
        current_date between
        cast('03-10-2013' as date format 'mm-dd-yyyy') and
        cast('11-03-2013' as date format 'mm-dd-yyyy')
    then 4
    else 5
end Offset,
(current_timestamp + cast(Offset as interval hour)) GMT

You have to actually cast the case statement's return value as an interval. I didn't even know interval types existed in Teradata. Thanks to this page for helping me along:

http://www.teradataforum.com/l081007a.htm

score 4 · Answer 2 · answered Jun 14 '13 at 00:05

4

If I understand correctly, you want to multiply the interval by some number. Believe it or not, that's literally all you need to do:

select  current_timestamp                    as right_now
      , right_now +      (interval '1' day)  as same_time_tomorrow
      , right_now + (2 * (interval '1' day)) as same_time_next_day

Intervals have always challenged me for some reason; I don't use them very often. But I've had this little example in my Teradata "cheat sheet" for quite a while.

answered Jun 14 '13 at 00:05

BellevueBob

9,498
5
29
56

I agree with you on the use of intervals. I can never recall how to use them and have to refer to a cheat sheet often. – Rob Paller Jun 14 '13 at 00:11
I don't want to hardcode the interval value as I mentioned in my question. I updated my question with a better SQL example. See if you can get it to work. – oscilatingcretin Jun 14 '13 at 11:53
Ahhhhaa! I figured it out. Will post as answer. – oscilatingcretin Jun 14 '13 at 12:00

score 0 · Answer 3 · answered Jun 27 '13 at 12:23

Two remarks:

You could return an INTERVAL instead of an INT

The recommended way to write a date literal in Teradata is DATE 'YYYY-MM-DD' instead of CAST/FORMAT

select
    case
        when current_date between DATE '2013-03-10' and DATE '2013-11-03'
        then interval '4' hour
        else interval '5'hour
    end AS Offset,
    current_timestamp + Offset AS GMT

How to add/subtract date/time components using a calculated interval?

3 Answers3