CODEWITHSUNDEEP
sqloracle-databasefunctionsysdate
tsvenbla
tsvenbla

Reputation: 442

PL/SQL function to add +7 days to SYSDATE

We're supposed to make a function that adds +7 days to the current SYSDATE, and also write the time and minute, however, my code only displays the date, not the time. What am I doing wrong? This is probably very easy, but I just can't figure it out, and there's not much help on the Internet either.

So far, I've tried:

CREATE OR REPLACE FUNCTION get_date(n IN NUMBER) 
RETURN DATE AS
BEGIN
  RETURN to_date(to_char(SYSDATE, 'DD.MM.YYYY HH24:MI'),'DD.MM.YYYY HH24:MI') + n;
END get_date;
/

So when you write (the 7 is the amount of days to advance):

SELECT get_date(7)
FROM dual;

Its result is this:

GET_DATE(7)
----------------
09.03.2016

However, as you can see, the time isn't included in the result, and that's what I need in this case. Any help at all would be appreciated. I'm sure I'm just too blind to see it, but I've stared for too long on this piece of code now, I'll admit my defeat.

Upvotes: 0

Views: 7896

Answers (3)

kevinskio
kevinskio

Reputation: 4551

You have to format the result to your specifications, as in

--create the function  
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER) 
RETURN DATE AS
BEGIN
  RETURN SYSDATE + n;
END get_date;

--call the function
SELECT TO_CHAR(get_date(7), 'DD.MM.YYYY HH24:MI')
FROM dual;

Or your new requirement of no formatting outside the function

--create the function  
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER) 
RETURN VARCHAR2 AS
BEGIN
  RETURN TO_CHAR(SYSDATE + n,'DD.MM.YYYY HH24:MI');
END get_date;

--call the function
SELECT get_date(7)
FROM dual;

Upvotes: 2

Egil Morten Eriksen
Egil Morten Eriksen

Reputation: 1

CREATE OR REPLACE FUNCTION get_date(n IN NUMBER) RETURN varchar2 is

dTmp DATE:=sysdate+n;

BEGIN

RETURN to_char(dTmp, 'DD.MM.YYYY HH24:MI');

END;

/

SELECT get_date(1) FROM dual;

Upvotes: -1

Aleksej
Aleksej

Reputation: 22959

You can decide if you want your function to return a date or a varchar; you may choose one of the following ways, depending on your need:

CREATE OR REPLACE FUNCTION get_date(n IN NUMBER) 
RETURN varchar2 AS
BEGIN
  RETURN to_char(SYSDATE  + n, 'DD.MM.YYYY HH24:MI');
END get_date;
/

CREATE OR REPLACE FUNCTION get_date2(n IN NUMBER) 
RETURN date AS
BEGIN
  RETURN to_date(to_char(SYSDATE  + n, 'DD.MM.YYYY HH24:MI'), 'DD.MM.YYYY HH24:MI');
END get_date2;
/

select to_char(get_date2(1), 'DD.MM.YYYY HH24:MI') from dual;

select get_date(1) from dual;

Upvotes: 1

Related Questions