Need help with adding an integer column to a date column in teradata

Analytics

Need help with adding an integer column to a date column in teradata

Hello,

I am trying to create a measure in business objects using the formula: (order_date + pflt)

where the order_date is a normal date and the pflt is an integer column in teradata

When I try to add the pflt to the date field, I get the error: Invalid date supplied for dbname.pflt

sql used: select order_date + pflt as duedate from dbname.viewname;

Can someone please help me with this query?

Thanks in advance,

Divya

4 REPLIES
Senior Supporter

Re: Need help with adding an integer column to a date column in teradata

Is it possible that order_date contains some high values?

Check Dieters comment in http://forums.teradata.com/forum/database/interval-subtraction-from-date-field-in-where-clause#comme...

Re: Need help with adding an integer column to a date column in teradata

order_date is in the format mm/dd/yyyy. And I cannot use INTERVAL as I dont have a specific integer that I need to add. It is a column in the table.

Senior Supporter

Re: Need help with adding an integer column to a date column in teradata

sorry Intervall was not the point.

What is the biggest date in your order_date column?

If it is  '12/31/9999' then it is the max possible date in TD. If you try to add 1 to this value you get the error message [Error 2665] Invalid date.

SELECT Cast('9999-12-31' as date)+1;

Enthusiast

Re: Need help with adding an integer column to a date column in teradata

Below Info from TD sql manual...Hope it helps!

DATE and Integer Arithmetic

The following arithmetic functions can be performed with date and an integer (INTEGER is

interpreted as a number of days):

• DATE + INTEGER

• INTEGER + DATE

• DATE - INTEGER

These expressions are not processed as simple addition or subtraction, but rather as explained in the following process:

1 The encoded date value is converted to an intermediate value which is the number of days

since some system-defined fixed date.

2 The integer value is then added or subtracted, forming another value as number of days,

since the fixed base date.

3 The result is converted back to a date, valid in the Gregorian calendar.