When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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:

  1. 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.
  2. 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!

    Return to user tips...


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article