Database Queries Using DATE

  • Thread starter Thread starter Nathan Sokalski
  • Start date Start date
N

Nathan Sokalski

I am doing a database query in Java, and it seems to work fine using any of
the fields except for the date. I am assuming that this has something to do
with my syntax, since the DATE type has the most complicated syntax. My
query looks like the following:

SELECT * FROM Employee WHERE BDATE='4/20/1981'

BDATE is the field name, which is obviously of type DATE, and the error I
recieve is:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria expression.

I am assuming that when it says "Data type mismatch" it is interpreting
'4/20/1981' as a string. But because that is the format I used in the INSERT
statements when I added the records to the table. If this is the problem,
what should my query string? Thanks.
 
SELECT * FROM Employee WHERE BDATE='4/20/1981'

Ugh. Use a standard format. If you were looking for April 11th, Access or
your client app could easily misinterpret it as November 4th. If you use a
standard and unambiguous format, that won't happen.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria expression.
Click to expand...

If you are querying Access, why are you posting to Java and SQL Server
groups? Learn how to use the tool you're using. Access requires dates to
be delimited by #, not ' ... try this, assuming your "date field" has no
time information:

SELECT * FROM Employee WHERE BDATE = '1981-04-20'

And BTW, don't use SELECT * in production code....

Followups set to microsoft.public.access.queries only.
 
I tried the syntax you suggested, but I recieved the same error.
--
Nathan Sokalski
(e-mail address removed)
Aaron Bertrand said:
SELECT * FROM Employee WHERE BDATE='4/20/1981'
Click to expand...

Ugh. Use a standard format. If you were looking for April 11th, Access or
your client app could easily misinterpret it as November 4th. If you use a
standard and unambiguous format, that won't happen.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria expression.
Click to expand...

If you are querying Access, why are you posting to Java and SQL Server
groups? Learn how to use the tool you're using. Access requires dates to
be delimited by #, not ' ... try this, assuming your "date field" has no
time information:

SELECT * FROM Employee WHERE BDATE = '1981-04-20'

And BTW, don't use SELECT * in production code....

Followups set to microsoft.public.access.queries only.
Click to expand...
 
I tried the syntax you suggested, but I recieved the same error.
Click to expand...

I think what Aaron intended to type (and did say, in the text) was

SELECT * FROM Employee WHERE BDATE=#4/20/1981#

i.e. using # rather than ' as a date/time delimiter.
 
Back
Top