All Forums Analytics
divyarajnarain 2 posts Joined 03/12
08 Mar 2012
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

 

ulrich 816 posts Joined 09/09
08 Mar 2012

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#comment-19318

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

divyarajnarain 2 posts Joined 03/12
08 Mar 2012

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.

ulrich 816 posts Joined 09/09
08 Mar 2012

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;

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Stefans 38 posts Joined 02/12
08 Mar 2012

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.

Stalin

You must sign in to leave a comment.