CODEWITHSUNDEEP
sqloracle11g
SQL_Student
SQL_Student

Reputation: 731

Add days Oracle SQL

SELECT ORDER_NUM, CUSTOMER_NUM, CUSTOMER_NAME, ADD_DAYS (ORDER_DATE, 20)
FROM CUSTOMER, ORDERS;

Oracle Express says ADD_DAYS invalid? Any ideas what Am I doing wrong?

Upvotes: 73

Views: 436904

Answers (6)

RaffyTaffy
RaffyTaffy

Reputation: 21

One thing about select (sysdate+3) from dual is that the sysdate is interpreted as date.

But if you want to use a custom date, not local, you need to make sure it is interpreted as date, not string. Like so (adding 3 days):

select (to_date('01/01/2020')+3) from dual

Upvotes: 2

Michael Kremser
Michael Kremser

Reputation: 195

Some disadvantage of "INTERVAL '1' DAY" is that bind variables cannot be used for the number of days added. Instead, numtodsinterval can be used, like in this small example:

select trunc(sysdate) + numtodsinterval(:x, 'day') tag
from dual

See also: NUMTODSINTERVAL in Oracle Database Online Documentation

Upvotes: 14

Mike
Mike

Reputation: 597

You can use the plus operator to add days to a date.

order_date + 20

Upvotes: 49

shah
shah

Reputation: 143

It's Simple.You can use

select (sysdate+2) as new_date from dual;

This will add two days from current date.

Upvotes: 12

Francesco Serra
Francesco Serra

Reputation: 813

In a more general way you can use "INTERVAL". Here some examples:

1) add a day

select sysdate + INTERVAL '1' DAY from dual;

2) add 20 days

select sysdate + INTERVAL '20' DAY from dual;

2) add some minutes

select sysdate + INTERVAL '15' MINUTE from dual;

Upvotes: 46

Manoj Kumawat
Manoj Kumawat

Reputation: 949

If you want to add N days to your days. You can use the plus operator as follows - 

SELECT ( SYSDATE + N ) FROM DUAL;

Upvotes: 83

Related Questions