CODEWITHSUNDEEP
sqlteradata
Thalles Ribeiro
Thalles Ribeiro

Reputation: 3

Teradata : Using Column reference with the Interval Parameter

I have a problem with a query, I want to subtract an interval to a date. The interval parameter is a value of a column.

HINIC               | IREFT | IPERDC

2022-06-28 00:22:15 | DAY   | 3

2022-07-10 20:00:39 | MONTH | 1

The result I wanted would be

HINIC               | IREFT | IPERDC | RESULT

2022-06-28 00:22:15 | DAY   | 3 | 2022-06-25 00:22:15

2022-07-10 20:00:39 | MONTH | 1 | 2022-06-10 20:00:39

I tried to used "interval" but it looks like it doesn't accept columns as parameters.

When I use "interval" with a column, like this

Select 

CAST(HINIC AS DATE) - INTERVAL IPERDC DAY 

from table

It returns this error

SQL Error [3707] [42000]: [Teradata Database] [TeraJDBC 16.20.00.06] [Error 3707] [SQLState 42000] Syntax error, expected something like a string or a Unicode character literal between the 'INTERVAL' keyword and the word IPERDC

Thank you.

Upvotes: 0

Views: 120

Answers (1)

Fred
Fred

Reputation: 2055

An expression such as (IPERDC * INTERVAL '1' MONTH) or CAST(IPERC AS INTERVAL MONTH(4)) would be syntactically valid, but not recommended. Using ADDMONTHS() for month and year calculations safely handles the fact that not all months have the same number of days.

You can safely use DAY intervals in that way, but Teradata allows DATE +/- INTEGER number of days directly.

CASE IREFT
  WHEN 'DAY' THEN CAST(HINIC AS DATE) - IPERDC
  WHEN 'MONTH' THEN ADDMONTHS(CAST(HINIC AS DATE),-1*IPERDC)
  WHEN 'YEAR' THEN ADDMONTHS(CAST(HINIC AS DATE),-12*IPERDC)
END

Upvotes: 1

Related Questions