User Tips: Using the ISO Date Format for International Dates
By Giuliano Sauro
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 US.
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
function.
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:
dd-MMM-yyyy (where 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
is "lug".
Now suppose a fairly common condition: we have this asp page written in
Italian, with 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:
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression 'Date=#1-lug-2002#'.
|
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 yyyy-mm-dd
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
Day(), Month(), and
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:
Day() and 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 If
statement checking if Len(string) < 2; if so, output a "0" and then the value
of Day()/Month().)
Why not put all this in a handy little function that does all this work for
us? Here there is the code, it checks if the passed variable is really a
date and then converts it in the ISO format.
Function JXIsoDate(dteDate)
'Version 1.0
If IsDate(dteDate) = True Then
DIM dteDay, dteMonth, dteYear
dteDay = Day(dteDate)
dteMonth = Month(dteDate)
dteYear = Year(dteDate)
JXIsoDate = dteYear & _
"-" & Right(Cstr(dteMonth + 100),2) & _
"-" & Right(Cstr(dteDay + 100),2)
Else
JXIsoDate = Null
End If
End Function
|
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
personalization... :-)
By Giuliano Sauro
asp@julius.it
Happy Programming!
