Subtracting value in one column from the date in another column

Question

I have tried:

add_months('date_column',  -'number_of_months_column') 

I get:

error [3535] A character string failed conversion to a numeric value.

Is what I am trying to do possible with the add_months option?

Answer 1

Why do you use single quotes around column names?

If number_of_months_column's data type is integer then this should work:

add_months(date_column, -number_of_months_column)

If number_of_months_column is a string then you must convert it first to an integer:

add_months(date_column, -to_number(number_of_months_column))

or:

add_months(date_column, -cast(to_number(number_of_months_column) as integer))

or:

add_months(date_column, -trycast(to_number(number_of_months_column) as integer))

Answer 2

Yes, I believe it is possible. But you need to make sure that the 2nd argument is a numeric data type. Based on the error, it looks like some records cannot be implicitly converted to a numeric.

The following details are from the Teradata Documentation regarding Error 3535 and its solution.

Explanation:

A character string constant in a query is in a context requiring that it represent a numeric value, and it does not.

Remedy:

Change the constant to either a numeric value or a character string that represents a numeric value.