Querying Access Dates with Leading Zeros

Kai

Hi

I have come across a strange symptom in one of my Queries. Im running quite a long SQL query on an access DB from an asp page, the problem is in the Where clause :

WHERE (Weekend=#18/09/2004# OR Weekend=#25/09/2004# OR Weekend=#02/10/2004# OR Weekend=#09/10/2004# Or Weekend=#16/10/2004# OR Weekend=#23/10/2004# OR Weekend=#30/10/2004#)


It finds all dates that have a day part higher than 9 just fine e.g. 12/12/2004.

but on dates where the day part has a leading 0 it wont find anything for them e.g. 02/10/2004

i have the local settings on the ASP page set to UK so it is in the same format. The Dates in the DB are identical to the date im passing in but it still wont find ones with leading Zeros ??


Anyone seen this before ?

tomED

It's been a long time since I had this problem, as I try and avoid access at all costs for websites!

There were two things I used to try:

Session.LCID = 2058 (which I think you have already set)

Or if the search is dynamic, rearranging the dates to mm/dd/yyyy instead of dd/mm/yyyy.

Hope this helps....

Kai

Yea turns out that Access was displaying the dates in UK format but they were actually stored in US format. How messed up is that, youd never know until you ran a query and checked your results.

Anyway re-arranging the dates in asp before running the Query sorted it out. Unfortunatly im stuck with Access for the time being.

DJB

Dates and access are a pain in the arse!

Work with ms sql and convert your dates to 20050114 format and you should have no problems. I think you can do the same with access.