CODEWITHSUNDEEP
oracle-databasefunctionplsql
BMW
BMW

Reputation: 630

Oracle compare two date

I have oracle query I want to compare two date it work fin but when I put it inside oracle function and pass a date as parameter I get this error:

ORA-01843: not a valid month
ORA-06512: at line 8
Process exited.

and this my function:

Create or Replace 
FUNCTION GET_MAX_VALUE 
(
  PLAN_DATE IN DATE  
, LOC IN NUMBER  
, RES IN NUMBER  
, TIS IN NUMBER  
) RETURN NUMBER AS 
units number;
return_val number;
BEGIN
  select ts.booked_units into units from tsm_transaction_tbl ts
  where ts.location_id=loc and ts.resource_id=res and ts.ts_id=tis and ts.trans_date=to_date(plan_date,'mm/dd/yyyy');

  RETURN units;
END GET_MAX_VALUE;

Upvotes: 0

Views: 754

Answers (1)

Rachcha
Rachcha

Reputation: 8816

Remove the to_date function that surrounds your plan_date variable.

You are already passing plan_date as DATE type variable. There is no need to convert it to date using to_date function.

Speaking about to_date function, it is used to convert a string (varchar2 type) to date type according to the format that we want. You can read more about to_date function on Oracle's site here.

Your code can be as following:

Create or Replace 
FUNCTION GET_MAX_VALUE 
(
  PLAN_DATE IN DATE  
, LOC IN NUMBER  
, RES IN NUMBER  
, TIS IN NUMBER  
) RETURN NUMBER AS 
units number;
return_val number;
BEGIN
  select ts.booked_units into units from tsm_transaction_tbl ts
  where ts.location_id=loc
  and ts.resource_id=res
  and ts.ts_id=tis
  and ts.trans_date = plan_date;

  RETURN units;
END GET_MAX_VALUE;

Upvotes: 2

Related Questions