Solved

Need help with a PL/SQL function: X days from today

Posted on 2004-09-22
5
1,131 Views
Last Modified: 2012-08-13
I need a function which one should be able to enter an amount of days as parameter(X) , and the function should be able to return the date which is X days ahead of SYSDATE.

Any help appreciated,
eX.
0
Comment
Question by:Gaute Rønningen
5 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12123547
try this:

create or replace function set_date( no_of_days in number)
return DATE
as
return sysdate + no_of_days;
end;
/
0
 

Author Comment

by:Gaute Rønningen
ID: 12123594
Errors for FUNCTION SET_DATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/16     PLS-00103: Encountered the symbol "+" when expecting one of the
         following:
         := . ( @ % ; not null range default character

So I'm guessing some other sign for '+' ?
0
 
LVL 2

Expert Comment

by:LeeMiller
ID: 12123703
Maybe this:

CREATE OR REPLACE
FUNCTION add_to_date
  ( days IN NUMBER)
  RETURN  DATE IS
     
   new_date DATE;

BEGIN
    new_date :=  SYSDATE+days;
    RETURN new_date ;
EXCEPTION
   WHEN others THEN
       dbms_output.put_line(sqlerrm) ;
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 250 total points
ID: 12123717
fix it : try this:


create or replace function set_date( no_of_days in number)
return DATE
as
test_date DATE;
begin
test_date := to_date(sysdate + no_of_days);
return test_date;
END;
/
0
 

Expert Comment

by:CedD
ID: 12123755
Try this :

create or replace function set_date( no_of_days in number)
return DATE
as
begin
declare dateret date;
begin

select sysdate+no_of_days
into dateret
from dual;


return dateret;
end;
end;
/


select set_date(12) from dual;

SET_DATE(12)
------------------
04/10/2004 17:07:23
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
help on oracle query 5 52
clob to char in oracle 3 62
How to create an alias for a column name in ORACLE sql ? 2 25
date show only hh:mm 2 25
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question