I write from Italy and I am a great fan of your useful site. I can say that
I have learned about 70% of what I know about ASP from your site and your
books. Being from Italy, one of the most frustrating things that can happen is
when trying to insert locale-specific dates into a SQL Server that may be hosted in
The problem arises because the USA date format is
mm/dd/yy while English and
European format is
dd/mm/yy. This leads the server to exchange days and
months and gives the most annoying errors when exporting an application
from a USA server to an European server.
Recently I read the article by Darren Neimke about international dates titled: Working with Databases and International Date Formats.
I would like to point out a few issues that have arised from using the suggested
Darren suggested that the issue of inserting European date formats into a US-located database could
be solved using the follow non abiguous date format:
MMM is the three-letter abbreviation of the month)
This can work flawlessy for countries of English language, but what about nations where English is
not the native tounge?
For example, the English abbreviation for
"July" is "jul", but in Italian the name is "Luglio" and the abbreviation
Now suppose a fairly common condition: we have this asp page written in
Session.LCID set to 1040 (Locale Identifier for Italy).
(For more information on the
Session.LCID property be sure to read:
Using the Locale Identifier (LCID).)
This ASP page shows dates and currency with Italian conventions, but the page is
hosted on a server with a US configuration. Argh! We end up getting errors when trying
to insert dates into the SQL Server database!
The query engine converting the date expects English names and, when receiving an Italian name, gives an error like:
You can imagine how this problem would occur with other countries who may have their own month names.
How can we wipe out this annoying problem once and forever?
There is an answer: the ISO date format. The ISO date format is not
ambiguous even in different countries. Its format is fixed to
and is understood by all the databases normally used by ASP. You must
notice that months and days under 10 must be preceded by a leading zero,
so, 02 instead of 2. Finally, the numbers are separated by dashes (
instead of slashes (
How can we format a date in this ISO format with correct leading zeroes? This is really quite simple when using VBScript's powerful built-in date-related functions:
- Step 1: Use the standard VBscript
Year()functions to get the day, month and year numbers, so that the conversion will depend from the international settings of the machine or the current LCID of the session.
- Step 2:
Month()will restitute a one digit number for days and months less than 10. How can we neatly add a zero in front of the number without using the old
If Len(string) < 2 Then ...?
Simple: we will add 100 to the number given by the function. If the function restitutes 2, we will have 102. Next, we will convert 102 into a string with
Cstr(102)and we'll have "102". Finally, to get only the last two digits:
Right("102",2)gives "02". That's it, now we have the leading zero. (Another way to do this is to just have an
Ifstatement checking if
Len(string) < 2; if so, output a "0" and then the value of
Converting the dates with this function before passing them to a SQL query
will save us really a lot of problems!
Finally, what's this
JX in front of the function? Oh well... only a
prefix in front of all my functions: I like to add a little bit of
|Return to user tips...|