Dates in SQL query to Oracle; how?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Dates in SQL query to Oracle; how?

  1. #1
    Jim Homa Guest

    Dates in SQL query to Oracle; how?


    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


  2. #2
    Bob Nolin Guest

    Re: Dates in SQL query to Oracle; how?


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


  3. #3
    Jim Homa Guest

    Re: Dates in SQL query to Oracle; how?


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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center