Home » Developer & Programmer » Reports & Discoverer » Due Date Calculation (Discoverer 11.1.7.0)
icon4.gif  Due Date Calculation [message #669641] Fri, 04 May 2018 12:43 Go to next message
DragonMouse
Messages: 2
Registered: May 2018
Junior Member
Good Afternoon. First time I'm trying this form...I'm trying to create a very simple formula to return a DUE DATE. In Excel it's simply DATE_RECEIVED + Standard (Date received is the date we've received the item and the Standard is how many days we are allowed to keep it before we have to return it)

I don't know why I can't seem to get past it. The 'Standard" is a calculation on it's own. DECODE(Priority by Category,'RADIOS',10,'VIDEOS',20,'ROUTERS',30). It returns: 10, 20, 30 depending on the 'product' it finds in the Priority by Category field. It works fine. But now I want to take the Date_Received and add the standard to return an Due Date. Received a Radio on 01-May-2018 and it needs to be out 10 days later on 11-May-2018 I want a calculation to return 11-May-2018
Re: Due Date Calculation [message #669644 is a reply to message #669641] Fri, 04 May 2018 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
SQL> SELECT TO_DATE('01-May-2018','DD-MON-YYYY')+10 FROM DUAL;

TO_DATE('
---------
11-MAY-18

Re: Due Date Calculation [message #669648 is a reply to message #669644] Fri, 04 May 2018 14:02 Go to previous messageGo to next message
DragonMouse
Messages: 2
Registered: May 2018
Junior Member
I don't appear to have a TO_DATE I have a TO_CHAR. the 01-May-2018 will come from the Asset Table (Date_Received) then I want to add the Standard Calculation to develop a date due. I don't have SQL only the Discoverer.

I tried fumbling since I didn't have a TO_DATE by using:

TO_CHAR(Assets.Date_Recieved, DD-MM-YYYY)+"Standard Calculation"

TO_CHAR(d[, fmt[, nlsparams]]) Converts a date d to a value of character datatype in the format
Re: Due Date Calculation [message #669653 is a reply to message #669648] Sat, 05 May 2018 02:18 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
select 
to_char(Assets.Date_Received + DECODE(Priority_by_Category,'RADIOS',10,'VIDEOS',20,'ROUTERS',30),'dd-mon-yyyy')
from assets;
Re: Due Date Calculation [message #669692 is a reply to message #669653] Tue, 08 May 2018 04:14 Go to previous message
cookiemonster
Messages: 13685
Registered: September 2008
Location: Rainy Manchester
Senior Member
And just to be clear why John's approach works and your's doesn't:
Oracle lets you add and subtract numbers from dates.
to_char takes a date and converts into a string that represents a date in a particular format - but the crucial thing is it is a string.
You can't add numbers to strings.
So you need to do the date math before to_char is applied.
Previous Topic: Add Additional Text after first print
Next Topic: Reports Server seems to be running reports slowly (merged 2)
Goto Forum:
  


Current Time: Tue Oct 15 01:15:38 CDT 2019