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



"tribalstorm" wrote:

> 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!!!
>
>