Date format in query

[Jaco K]

I have the following query

SELECT * FROM Orders WHERE OrderDate Between #4-9-2006# And #4-10-2006#

I expect rows with OrderDate = 5-9-2006 to be returned, however they are not.
Apparently the date formats are interpretted as mm-dd-yyyy instead of dd-mm-yyyy.

Can I set the default date format or is there another solution?

 

[freakazeud]

re:

Hi,
check this solution.
HTH
Good luck

 

[cuttsy]

When evaluating dates it will always trys the american format of mmddyyyy unless it doesn't make sense i.e. 30/09/2006 in which case it switches to UK format ddmmyyyy.

It is very annoying and for this reason I always use ISO date format in my applications yyyy-mm-dd