Need help on my SQL statement with date criteria

[Ajax36]

I've this appl using VB.Net 2005 as front-end and MS Access as my DB. I have a table with a date field defined using the date format as "short date'. When look at it under MS Access, the data shows data in mm/dd/yyyy format (e.g. 12/23/2006"

I tried the following SQA statement to retrieve a set of records based on the following sample date range:

First attempt:
SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= '12-15-2006' AND WO_Creation_Date <= '2-6-2007'

Then tried
SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= '12/15/2006' AND WO_Creation_Date <= '2/6/2007'


None of them work, I got an error message "Datatype mismatched in criteria expression.

Can someone please tells me where is my problem ?

[techgnome]

Access?? Access uses # to set off dates....

SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= #12-15-2006# AND WO_Creation_Date <= #2-6-2007#

Also, you may want to consider a less anbiguous date format, like yyyy-mm-dd .....
SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= #2006-12-15# AND WO_Creation_Date <= #2007-2-6#

-tg

[Daystar]

Access?? Access uses # to set off dates....

SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= #12-15-2006# AND WO_Creation_Date <= #2-6-2007#

Also, you may want to consider a less anbiguous date format, like yyyy-mm-dd .....
SELECT * FROM [tblWork_Order] WHERE WO_Creation_Date >= #2006-12-15# AND WO_Creation_Date <= #2007-2-6#

-tg

In access it is best to use Between

Select * from [tblWork_Order] Where WO_Creation_Date Between #12/15/2006# and #02/06/2007#

[jmcilhinney]

There's no such thing as an ambiguous date format in SQL. SQL only understands U.S. format, i.e. MM/dd/yyyy. ALL date literals in SQL code MUST be in that format, although single digit months can have a leading zero or not.

Also, just because Access only DISPLAYS the date portion doesn't mean that the field doesn't CONTAIN a time portion too. If you have saved your values with a time portion then the database fields will contain that time as well as the date.

[Daystar]

jmchilney, Access can display the time portion too.

[jmcilhinney]

jmchilney, Access can display the time portion too.

I didn't say that it couldn't. What I said was that your setting the properties of a column to display only the date portion doesn't mean that it doesn't have a time portion.

[si_the_geek]

There's no such thing as an ambiguous date format in SQL. SQL only understands U.S. format, i.e. MM/dd/yyyy. ALL date literals in SQL code MUST be in that format, although single digit months can have a leading zero or not.

I'm afraid I'll have to technically disagree there.. 12/15/2006 and 15/12/2006 both work (and have identical values) for the majority of DBMS's. You will however get problems if you try to use 07/02/2007 to mean the 7th of Feb (as it will be interpreted as 2nd of July, no matter what your PC/database/.. settings are).

The format that TG suggested (yyyy-mm-dd) is just as valid as MM/dd/yyyy, I believe it is also part of the SQL89/92 standard for dates. The difference is that if you forgot to format the date, the cause of problems is clearer.

[jmcilhinney]

I bow to superior knowledge. Sorry for doubting you tg.

[techgnome]

a thousand lashing with a wet noodle for you.... and don't let it happen again.

As it is, it's only been in the last few weeks that I've been using the yyyy-mm-dd format as I've been dealing with some truly whacked formats (where EVERYTHING, and I mean EVERYTHING was sent as text).


-tg