Using Dates in PL/SQL [duplicate]

Question

I am very new to using PL/SQL and I have created a procedure but I cannot figure out the correct syntax in order to get the current month and year's data and another cursor to get the data from exactly 1 year prior:

create or replace procedure data(acc integer, month integer, year integer)
as


Cursor c1 is 
select usage
from bill
where account =acc_num and to_char(BILL_DATE, 'MM-YYYY') = 'month-year';


Cursor c3 is 
select usage
from bill
where account =acc_num and
     to_char(BILL_DATE, 'MM-YYYY') = 'month-year' - 1;

** I do understand this is only part of the code, but I believe my logic is almost complete for finding the data I want. Using PLSQL

It's the same syntax as in plain SQL and you already have an answer for that. — user330315
– user330315, Commented Dec 17, 2016 at 15:33

Gordon Linoff · Accepted Answer · 2016-12-17 15:29:43Z

2

I think you are looking for something like this:

select usage
from bill
where account = in_account and
      extract(year from bill_date) = in_year and
      extract(month from bill_date) = in_month;

If you want to compare the year and month (which are passed in as integers), just extract those attributes from the date.

If you are learning PL/SQL, learn to name your parameters and arguments so you can distinguish them from columns:

create or replace procedure data (
    in_account integer,
    in_month integer,
    in_year integer
) as
begin
    . . .

(And "data" is a very curious name for a stored procedure. I would expect a verb in the name.)

edited Dec 17, 2016 at 15:29

answered Dec 17, 2016 at 15:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2402107 Over a year ago

so you would just do like in_year-1?? i am not understanding how to subtract one year

Gordon Linoff Over a year ago

To get the previous year, you would use in_year - 1. in_year is an integer (such as 2,016).

Collectives™ on Stack Overflow

Using Dates in PL/SQL [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related