5 replies to this topic

[nick3]

Hey!

I have a fast question about some code of mine that doesnt work. I have an SQL query looking like this that wont work

SELECT * FROM data WHERE datefield=2010-09-02

I dont get an error msg but I dont get the row that contains that specific date in the DB. If I put ' around the date like

SELECT * FROM data WHERE datefield='2010-09-20'

I get the error 3464: Data type mismatch... In the DB datefield is specified as "Short date"

Would be great if anyone wanna tell me whats wrong :)

[dbug]

What is the field type of datefield ? probably it's of a type that includes the time. If you do a query specifying only the date, the time is assumed to be 00:00:00 (on some data types even the milliseconds can be present). If the table does not contain any row with that exact time, the query won't return anything.

You should transform datefield to a pure date in the WHERE clause to filter out time. It depends on the database you are using, but the general idea would be:

SELECT * FROM data WHERE convert_to_date_only(datefield)=2010-09-02

You should find an equivalent of convert_to_date_only for your database.

[nick3]

Hmm...thanks for replying... Im using an Access database, the type of the field is "Shot date" so should just be the date, but even if I try with the 00:00:00 after it wont work. I cant find a convert to date only function, I found format but dont know how that works really, this wont work

SELECT * FROM data WHERE Format(2010/09/02,YYYY/MM/DD) As datafield

If I use this i get the error:
Runtime error 3075. Syntax error
(missing operator) in query expression
Format(2010/09/02,YYYY/MM/DD) AS datafield

[dbug]

If I'm not wrong, Access treats dates as numbers, so when you do the comparison it really is calculating 2010-09-02 = 1999, which corresponds to some date, but surely not what you want.

Also you can use this format to represent dates in Access:

SELECT * FROM data WHERE datefield=#2010-09-02#

I'm not sure if '-' is allowed. If it doesn't work, use '/' to separate year, month and day.

[nick3]

Awesome! :)

Works like a charm, thank you very much

[Orjan]

dbug said:

I'm not sure if '-' is allowed. If it doesn't work, use '/' to separate year, month and day.

Of course '-' works, it's the ISO standard of date format.