Dates in SQL query to Oracle; how?

[Jim Homa]

I am trying to pass a date to Oracle 7.3.4 from VB 6 to create a recordset.
I cannot get the SQL
syntax right for a query like SELECT * FROM Employee WHERE HireDate > '1/1/00'.
This seems
to be correct as indicated by an Ask the VB Pro entry titled "Passing Dates
to Databases from
ASP" written by Rama Ramachandran. I know it's for ASP, but I think the
syntax should be similar.

If I use that syntax and examine the fields in the Watch window, all of the
field values are ""
with the exception of HireDate which has the value <Errors occurred>. If
I use a text
field (e.g. LastName) in a new query, the results are fine. Access 2000 works
fine as expected
by Rama's answer when using SELECT * FROM Employee WHERE HireDate > #1/1/00#.

I tried using an ADO Data Control for troubleshooting purposes. I set the
Command Text (SQL)
to SELECT * FROM Employee WHERE HireDate > '1/1/00' on the RecordSource tab
of the Property
Pages dialog box for the ADO Data Control. This seemed to work since I was
able to choose a
field for a databound text box. However, when I try running the application
I get the following
error: Unable to bind to field or DataMember: 'HireDate'. I then changed
the field to LastName
and got the error: Unable to bind to field or DataMember: 'LastName'. Again,
if used a text field
in the WHERE clause, the ADO Data Control works as expected.

My hunch is that Oracle doesn't like when the date is converted to a string
before I pass it. I may
be totally wrong, but I sure would appreciate some help on this apparently
simple issue.

Thanks,
Jim

[Bob Nolin]

>I cannot get the SQL
>syntax right for a query like SELECT * FROM Employee WHERE HireDate > '1/1/00'.

Try this:

SELECT * FROM Employee WHERE HireDate > to_date('1/1/00', 'mm/dd/yy')

This tells Oracle how to interpret the date.

[Jim Homa]

Bob,
You da man!!!!!!! At first, the errors were gone but the recordset did not
contain the correct records.
I fiddled with various formats - all of them containing 2 digit years. No
go. Then I tried the following:

"SELECT * FROM Employee WHERE HireDate < to_date('01-JUN-1994','DD-MON-YYYY'')"

Bodda bing!!!! The 4 digit year appears to have done the trick. Thanks again.
Have a great weekend!!!



"Bob Nolin" <bob_nolin@dcsg.com> wrote:
>
>>I cannot get the SQL
>>syntax right for a query like SELECT * FROM Employee WHERE HireDate > '1/1/00'.
>
>Try this:
>
>SELECT * FROM Employee WHERE HireDate > to_date('1/1/00', 'mm/dd/yy')
>
>This tells Oracle how to interpret the date.
>