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,
Is it possible that order_date contains some high values?
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.
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;
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.