How to pass date values in oracle?

By default the values are coming like this

activated_date.date_value as "Activated Date", completed_date.date_value as "Completed Date",

2015-11-25 05:34:57 2015-11-25 05:35:03

How to pass date value in MM/dd/YYYY format for a package body Here is the package body

pkg_campaign_interactions_qty.getCampaignIncomingQty(Obj.object_id,to_date(to_char(activated_date.date_value,'MM/dd/YYYY'),'MM/dd/YYYY'),to_date(to_char(completed_date.date_value,'MM/dd/YYYY'),'MM/dd/YYYY'))as "Interactions"

I tried to pass date values this format getting ORA-01843: not a valid month

to_date(activated_date.date_value,'MM/dd/YYYY') as "Activated Date",to_date(completed_date.date_value,'MM/dd/YYYY') as "Completed Date",

This is my package body

CREATE OR REPLACE PACKAGE "PKG_CAMPAIGN_INTERACTIONS_QTY" as FUNCTION getCampaignIncomingQty(tableName IN VARCHAR2,ActivatedDate IN DATE,CompletedDate IN DATE) RETURN NUMBER end PKG_CAMPAIGN_INTERACTIONS_QTY;

/

---

The error message you get means that the value you are trying to turn into a date does not have the format specified.

to_date(activated_date.date_value,'MM/dd/YYYY')

Will only give you a date when activated_date.date_value is a string that contains a date in MM/dd/YYYY format.

Not a valid month indicates that the value of the first part of the string is not a number between 1 and 12.

Based on the sample you gave you should use:

to_date(activated_date.date_value,'YYYY-MM-DD')

So if you have a function that has an input parameter of type date, you can call it in the following way:

create or replace my_function(p_date date) return number...

declare
  my_number number;
  my_date_string1 varchar2(20);
  my_date_string2 varchar2(20);
begin

  -- some string representing dates in different formats.
  my_date_string1 := '2015-12-01';
  my_date_string2 := '15/11/2015';

  -- Call the function. Make sure to convert the string into a
  -- date using the correct format.
  my_number := my_function(to_date(my_date_string1,'YYYY-MM-DD'));
  my_number := my_function(to_date(my_date_string2,'DD/MM/YYYY'));
end;

链接地址: http://www.djcxy.com/p/6304.html

上一篇: 从Oracle PLSQL调用java时如何将参数传递给java

下一篇: 如何在oracle中传递日期值？