-1

I want to write a PLSQL query in the following way:

Select ...
FROM ...
WHERE
Date = '06/FEB/2011' + 7(days)

Im new to PLSQL, I've seen solutions such as date+1, but that requires by date to be inside a variable. I tired putting into a variable like the following:

declare date1  date;
begin
date1 := '06/FEB/2015';
select *
from 
    ...
where
        DATE_LABEL = date1 + 1;

end;

But it keeps giving me an error that is expecting an INTO statement in the SELECT. Im not sure what this means.

Thanks

jmich738
  • 1,565
  • 3
  • 24
  • 41

3 Answers3

2

  1. The DATE data type is not stored as a string, so hard-coding to a specific format is not advised, as the format may change based on your database/session NLS_DATE_FORMAT value. A more portable expression is to include the data format mask in the query, such as:

    select * 
from table Foo
where date_col = to_date('06/FEB/2011','DD/MON/YYYY) + 7;

The date format masks values are documented here.

  1. When executing a query within a pl/sql block, you have have to define somewhere for the result set to be stored upon return. You need to use a cursor for that. In your example, if you only have one row to return, you can declare a placeholder for the result as you did, but need to use the INTO keyword in you select:

    declare date1  date;
begin
date1 := '06/FEB/2015';
select date_col
into   date1
from  table  -- etc.    
end;

I would suggest to read some PL/SQL tutorials, which cover these basic concepts in detail.

sqluser
  • 5,502
  • 7
  • 36
  • 50
OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
2

Actually, an even more portable solution is to use the standard Ansi date literal. This will be recognized by a large number of DBMSs. So your code would look like this:

Select  ...
FROM    ...
WHERE   DateField = date '2011-02-06' + 7;

or

WHERE   DateField = (date '2011-02-06') + 7;

just to make it clearer.

The date literal must be in the form 'yyyy-mm-dd' and is not affected by system date format settings.

TommCatt
  • 5,498
  • 1
  • 13
  • 20
0
ADDDATE(date, INTERVALE INTEGER DAY)

You should write the number of days also you can use Month, Minute, Hour.

Muhammad Saqlain
  • 2,112
  • 4
  • 33
  • 48
Mouhamed Habib
  • 1