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.
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;