.net oracle managed provider date issue

  • Thread starter Thread starter tribalstorm
  • Start date Start date
T

tribalstorm

Hi,

The following query runs fine in Oracle SQL Developer, it also runs fine
using the .net oracle managed provider on my local dev machine. However, it
errors out when I run it on our web server with the following error message:

ORA-01830: date format picture ends before converting entire input string

I've ran into a similar issue about dates a few weeks ago...and tracked it
down to that the web server seemed to somehow utilize a slightly different
date format than my dev machine, I believe it had to do with if a zero was
put in front of a date--ie.e 5/13/08 vs 05/13/08.

I thought this was weird, but worked around it with my code...now it's
happening again, and I'd like to actually understand and fix what is really
going on.

I thought maybe there were different versions of the driver on my dev box vs
web server, but I looked at object browser for system.data.oracle client on
both boxes and they say 2.0.50727...

Here is the sql (is the same is generated locally and on web server)

Select to_char(as_of_date,'mm/yyyy') as dt, round(avg(rate),4) as
rate,upper(index_name) as scenarioname
FROM CMRG_DATA.VW_GETDATA_MARKET_RATES where upper(index_name) like
'PMMS30W' AND (AS_OF_DATE
BETWEEN TO_DATE('01/01/2004,01/01/2004','MM/DD/YYYY') AND
TO_DATE('01/01/2005,01/01/2005','MM/DD/YYYY'))
group by to_char(AS_OF_DATE,'mm/yyyy'),index_name

Thanks anyone for your help!!!
 
tribalstorm,

Since the query works in other environments your to_date function must be
valid.

But all of my documentation says the function takes 2 arguments: a date and
a format string. Yet you supply the date argument as 2 dates separated with a
comma:

'01/01/2005,01/01/2005'

How does that work?

Kerry Moorman
 
Back
Top