ASP, Access, UK Dates... lots of fun

[wlm_psl]

I have been trying my hardest to resolve this looking up the various different websites and articles however all the fixes suggested dont seem to work.

I am trying to interact with an Access MDB Database, through ASP using SQL queries on an IIS Web Server.

The issue comes when I want to input or compare a date.

I have tried setting all the regional settings (both in CP and RegEdit) on the IIS Server Machine to EN-GB and the date formats to match.

I have tried changing the LCID to 2057 for GB dates.

No matter what I try, it just doesnt seem to work.

One specific example is this

sSQLGP = "SELECT * FROM orders WHERE orderdate BETWEEN " & sStartDate & " AND " & sEndDate & " ORDER BY orderdate ASC"

sStartDate is set (for example) to #01/05/2011#
sEndDate is set (for example) to #31/05/2011#

Now when I try and echo the results for this query, I get results from the 5th Jan, not the 1st May

I even tried to split it apart using datepart so that it read 05/01/2011, however for some stupid reason, it returns the same results switching it back to 5th Jan!!!

The ONLY workaround I have, which is crazy and not ideal when dealing with dates across the entire code of the site is to SWITCH the LCID to 1033, build my dates into the variables, run the query and then SWITCH the LCID back to 2057

It is VERY frustrating and I can not believe that there is no real fix for this.

I know there must be something that can be done as until recently we used to have our domain hosted at FASTHOSTS, they offered support for dealing with MDB files and whenever I performed queries there, I used UK dates and didnt get a single problem. So if they can do it at a server level (since I didnt have to add any LCID changes or such when I used their servers) then there must be a way to set this at our server level.

I can use the workaround, but I shouldnt have to

 

[Beetle]

Not sure if it will help in your case, but you might have a look at;

http://allenbrowne.com/ser-36.html

see section 2. Wrong Formatting in Code

 

[wlm_psl]

Not sure if it will help in your case, but you might have a look at;


see section 2. Wrong Formatting in Code

I had already seen this, and it is as broad as it is long. If I have to create a function, I may as well just toggle the LCID before and after it deals with a date since this seems to have the same job.

What I want is to select something either within MSACCESS settings, IIS settings or a specific run once script include that will make sure any date I give is treated in the correct way.

Like I said, I know it can be done at a server level, since my previous domain hosts FASTHOSTS did this

 

[spikepl]

I'd like to know the trick from FASTHOSTS, to compel Access to accept dd-mm-yyyy in queries

Are you sure that it ever worked? When I did .NET websites on Access and IIS, I too had to do the date conversion trick, but of course only when explicitly and manually going to string representation of dates in SQL's statements. Variables holding dates are just fine, no matter the locale settings.

For manual input into Access SQL one can also use 1-May-2011 or 2011-05-01 - both are unambiguous.

 

[wlm_psl]

I would also like to know this trick!
All I know is that I didnt have to ask or prompt them to do anything... all I know is that when dealing with any date based ASP VB Script and an MDB database that I could use UK dates without a problem.

Since they dont ask me to add anything into my scripts, it must be something server side however what!

We no longer use them, but even if we did since even trying to get them to fix a downed connection takes 50 emails, it is doubtful they will be able to answer this issue.


Hey Ho!! So for now, all I can do is this LCID toggle, very frustrating