1

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
InvisibleInk
  • 43
  • 1
  • 8
  • Please post sample data and expected output – Arun Palanisamy Dec 17 '20 at 14:34
  • What are the data types of the two columns? Is the first one in fact a DATE and the second numeric (e.g. INTEGER)? Seems at least one is a character string and an implicit data type conversion is being attempted. – Fred Dec 17 '20 at 15:41

2 Answers2

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))
forpas
  • 160,666
  • 10
  • 38
  • 76
0

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.

EbixG
  • 66
  • 1
  • 5
  • Welcome to Stack Overflow! Could you update your answer with a quote from the link you are referring to? Otherwise, in case the link becomes invalid, your answer will be of no use to others. Thanks! – Nikolay Shebanov Dec 26 '20 at 15:51