OraFAQ Forum: Reports & Discoverer » Due Date Calculation

Home » Developer & Programmer » Reports & Discoverer » Due Date Calculation (Discoverer 11.1.7.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Due Date Calculation [message #669641]

Fri, 04 May 2018 12:43

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

Report message to a moderator

Re: Due Date Calculation [message #669644 is a reply to message #669641]

Fri, 04 May 2018 13:04

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Due Date Calculation [message #669648 is a reply to message #669644]

Fri, 04 May 2018 14:02

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

Report message to a moderator

Re: Due Date Calculation [message #669653 is a reply to message #669648]

Sat, 05 May 2018 02:18

John Watson
Messages: 8964
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;

Report message to a moderator

Re: Due Date Calculation [message #669692 is a reply to message #669653]

Tue, 08 May 2018 04:14

cookiemonster
Messages: 13963
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.

Report message to a moderator

Previous Topic:	Add Additional Text after first print
Next Topic:	Reports Server seems to be running reports slowly (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:20:10 CST 2025