Use CDbl for dates between asp, vbscript, and MS Access - ΩJr. Software Articles and Products

The problem

MS Access' SQL-engine expects an American date notation for a date field. In the Netherlands, we use the Dutch date notation for display and entry. This tends to result in a switch of the day and month values on an ASP web page. If this happens to you, I have got the ultimate solution.


Background

I'm using the UK version of MSACC97. I have a database with a table "tblNews", which sports 2 fields: NDate (a date) and NBody (a text). The display format of NDate is set to "short date". This equals my Windows date display format: "d-m-yy".

I also have 2 ASPs which employ MS VBScript. The first creates an overview of records in tblNews, sorted descendingly by NDate. The webmaster can click any date, to edit the accompanying news article in the 2nd page. The connection is made by passing the NDate in the querystring of the 2nd page.

The edit page retrieves the news article which has the same date as passed in its querystring. It also allows editing of that same date. Therefore it sports a text field that displays and allow editing of the news article date.

If I display the NDate without any alterations, MS Access will yield a default date type, formatted internationally. Access and ASP ignore the format setting of the NDate field in the tblNews table. The date is handled by asp.dll, the web server library that (by default) handles all ASP scripts. This library changes the date into a string, to be shown via HTML. It looks at the date format set globally in the web server. In my case, that's American. Therefore, the overview shows American dates.

If I wish to change the display, I can generate a different date notation format. Luckily, the recordset showing the list still knows the date in standard date type. This we can extrapolate with the Day(), Month(), and Year() functions of VBScript. The result is a string we can put into HTML, which allows me to generate a Dutch date notation.

Unfortunately, this does not let me pick the correct news article. Why? Because the Access SQL-engine expect American notation in its WHERE-clause. Thus, the querystring passed to the edit page seems to need to receive American notation.

Now the edit page collects the article, and displays it. The webmaster changes the date, saves the article, and lo and behold: the day and month of the article date have switched. For the entry is in Dutch format, while Access expects American.


One failed attempt to solve this...

One would expect to read the date input from the submitted edit page, pick it apart using Day(), Month(), and Year(), place them in American order (mm-dd-yyyy), and pass that to the SQL instruction. Alas, the day and month continue to trade places. Why? Because the asp.dll uses the American global date notation, specified at server level, to understand the input date, and reads the first number as month, while I intend it as a day. That's how Day() and Month() are supposed to work.


Another failed attempt...

So you think: you know what that Dutch date format looks like. Why not use the Mid() function to extract the correct numbers from the input date and shuffle them into American format for the SQL instruction? Unfortunately, we're still relying on date interpretation by asp.dll, which plays revolving doors before we can touch it with Mid(). And if you do not build up a date at all, but allow entering day, month, and year separately, you open up your database to sql injection. The last thing I'm looking forward to is adding even more checks to my code.


A working solution: store it.

So yes, pulling apart the input into 3 separate input boxes is actually the start of the solution. That way we completely avoid date interpretation. We combine them into a date by using DateSerial(), which yields international date notation. I want to reformat that to American.

And then I remember that Microsoft Access does not store a date as a date at all. Instead, it stores dates as Double Integers... independent of any date notation. So I placed a CDbl() around DateSerial(), and passed the numerical result of this to the SQL instruction. It worked! One can simply forego the hassle with varying date formats by storing the date as the Double used by Access internally.


Same working solution: display it.

This forces me to also separate the day, month, and year on the overview. The Day() and Month() functions fail here too, for the same reason as before. (Sure, I could change the date notation setting for the server... and next week some other application needs a different date format and the server gets changed, causing my app to fail unexpectedly. This did happen in my case. No, thank you.)

What if storing the ate as a double means I can apply Day() and Month() on it as well, inside the SQL instruction, before getting pushed about by asp.dll? Yes! That does indeed work. It also allows me to pass the entire double integer as the news article identification, instead of a date string, and subsequently requires me to change the edit page so it recognises news articles by that double, rather than a date.

Problem solved by finding a way around it.


Conclusion

If you find yourself fumbling with the display and storage of various date notation formats across languages, forget about the date itself, and switch to the double integer Microsoft Access itself uses internally, for use in your scripts and sql.