CODEWITHSUNDEEP
sqloracle-databasedate
COL
COL

Reputation: 27

How to pass a variable in oracle and return the date

In oracle if we 

SELECT to_char(sysdate) FROM dual

execute this query we will be getting the current date.

But for this I want to pass a variable like say if I pass 2 I want to get the date 2 days back.

If pass 3 I want to get the date 3 days back.

How can i achieve it in oracle?

Upvotes: 0

Views: 234

Answers (2)

Анатолий Предеин
Анатолий Предеин

Reputation: 571

you cat create function like this

create or replace function getDate(p_dNum number:=0) return date is 
begin
  return sysdate - p_dNum;
end;

and then call function in your sql like this

select to_char(getDate(p_dNum => 2),'yyyy/mm/dd') 
  from dual;

Upvotes: 0

Pirate X
Pirate X

Reputation: 3095

Upon executing the following script, SQL Developer will ask for value of x. (Press F5, don't use CTRL+ENTER)

Query

SET SERVEROUTPUT ON
select sysdate - &x from dual;
undefine x;

I entered value as 2, it returned the value 28 Jun 2016

Output

old: select sysdate - &x from dual
new: select sysdate - 2 from dual

SYSDATE-2
---------
28-JUN-16
1 row selected.

Upvotes: 1

Related Questions