problem executing stored procedure from vb

[sourabhjha]

I am executing a stored procedure in oracle from VB.
One of the parameters of the stored procedure takes a comma delimited string with date fields
in it.When i execute that stored procedure from SQL plus using a pl/sql block the procedure is
successfully executed.But if i execute the same stored procedure from vb using command objecy
then i get the error saying "ORA-01847: month's day must be between 1 and the last month's day "
i tried using to_date function also in the stored procedure....but that does not help....
Any clue
thanks in advance
-Sourabh

 

[Bveoracle]

This has something to do with default dateformats.

ALWAYS explicitly format (and unformat) dates. So use TO_CHAR (sysdate, 'YYYYMMDD') to build your (varchar2)parameter and use TO_DATE (my_date, 'YYYYMMDD') to get it back as a date.

This will improve portability of your code to databases with different date formats (here in holland we often use DD-MM-YYYY).

Gr. Bart.

 

[sourabhjha]

i am already doing that.....
this is where i format the fields before sending them to sql...vb code::
"element = Format(element, "MM-DD-YYYY&quot"

and below is the part of code from the the stored procedure..
"insert into historical_data values(config_id,geo_key,pro_key,to_date(SingleDate,'MM-DD-YYYY'),data_measure_id,SingleData,db_msr_no);"

...here the error is says...invalid momnth....

sample date string going to stored proc is like this...
"01-01-1995,02-01-1995,03-01-1995,04-01-1995,05-01-1995".

 

[mesuj]

I think it takes the whole String and tries to convert it in the given format. Did you check the parsing of the String?.May be thats where you need to revisit.