Microsoft Query & Dates from Oracle
 

I am pulling data from an Oracle db into excel and am
writing the query in Microsoft Query accessed through
Excel. I am trying to utilize user specified criteria for
the date but it seems like there is a formatting issue. MS
Query keeps saying:

ORA-01843: not a valid month

I even copied a date from the unfiltered data displayed
and pasted it into the user entry field but still received
the same error. The data is showing up in the results
section as:

2000-05-01 00:00:00

But when I enter this into the Value field for MS Query it
changes it automatically to:

#05/01/2000#

which does return the correct information, but then the
user cannot specify what they want to enter at anytime
without modifying the query. I have had a lot of success
with SQL ODBC connections and tables, but this is the
first time I have tied to pull from Oracle. Im I making
some sort of newbie mistake? Any sugestions would be
greatly appreciated as I have tried about every format I
can think of for the user entered data and I keep getting
the invalid month error.

Thanks

Matt

Microsoft Query & Dates from Oracle
 

I was able to find out that I need to use Oracle's date
format which is:

DD-MMM-YY

That made all the difference. Thanks Tony M.

Matt D.

-----Original Message-----
I am pulling data from an Oracle db into excel and am
writing the query in Microsoft Query accessed through
Excel. I am trying to utilize user specified criteria for
the date but it seems like there is a formatting issue.
MS
Query keeps saying:

ORA-01843: not a valid month

I even copied a date from the unfiltered data displayed
and pasted it into the user entry field but still
received
the same error. The data is showing up in the results
section as:

2000-05-01 00:00:00

But when I enter this into the Value field for MS Query
it
changes it automatically to:

#05/01/2000#

which does return the correct information, but then the
user cannot specify what they want to enter at anytime
without modifying the query. I have had a lot of success
with SQL ODBC connections and tables, but this is the
first time I have tied to pull from Oracle. Im I making
some sort of newbie mistake? Any sugestions would be
greatly appreciated as I have tried about every format I
can think of for the user entered data and I keep getting
the invalid month error.

Thanks

Matt

.

Microsoft Query & Dates from Oracle
 

"Matt D." wrote ...

I was able to find out that I need to use Oracle's date
format which is:

DD-MMM-YY

That made all the difference.

Is that really 'Oracle's date format' or are you merely making the
month unambiguous? Is it possible for the DBA to change 'Oracle's date
format' or is it always the same?

--

Microsoft Query & Dates from Oracle
 

I believe there is a session level variable called nls_date_format, which
defaults to dd-mmm-yyyy

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"onedaywhen" wrote in message
om...
"Matt D." wrote ...

I was able to find out that I need to use Oracle's date
format which is:

DD-MMM-YY

That made all the difference.

Is that really 'Oracle's date format' or are you merely making the
month unambiguous? Is it possible for the DBA to change 'Oracle's date
format' or is it always the same?

--

Microsoft Query & Dates from Oracle
 

"Rob van Gelder" wrote ...

Is that really 'Oracle's date format' or are you merely making the
month unambiguous? Is it possible for the DBA to change 'Oracle's date
format' or is it always the same?

I believe there is a session level variable called nls_date_format, which
defaults to dd-mmm-yyyy

It was meant as a heads up to the OP, along the lines of:

http://groups.google.com/groups?selm...g .google.com

This is your captain speaking. The irony filter has now been
extinguished.

--