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

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Tuesday, April 10, 2001

Working with Databases and International Date Formats, Part 2

By Darren Neimke

  • Read Part 1

  • In Part 1 we looked at how a database stores date information internally. In this part we'll examine how the Query Engine converts a string into the underlying date format and how to present the Query Engine with a date format that will always work, regardless of database settings or LCID settings!

    - continued -

    Now that we have a firm understanding of how Dates are stored in the database, let's think back to the String that we passed to the Query Engine. Keep in mind that we didn't pass the Query Engine a date - we passed it a String. When the Query Engine works through our SQL statement String and gets to:

    fldDateOrdered = #10/04/2001#

    It says: "OK we are going to put #10/04/2001# into the fldDateOrdered field and because it is surrounded by date delimiters (#) I must do an explicit conversion to the underlying value." This underlying value (36991) is what will actually be stored in the database. This is where the Query Engine works on the fact that Dates are based on the American premise of mm/dd/yyyy, there is no way of getting around this.

    The common misconception is that you can change the way the database Query Engine expects the date format by changing the format of the field in the table to display the dates as Medium, or Long Formatted Dates. However, regardless of the formatting option that you choose for the field, the underlying number stored in the database will remain the same and the Query Engine still expects the date in the American premise of mm/dd/yyyy. Therefore if you generally think of Dates in the format of dd-mm-yyyy (the non-American standard) then you will be in for a confusing time when converting Dates between your ASP page's Locale ID and the Database Query Engine.

    Now that I've explained the problem, I'll show you how to fix it. You need to convert your Date to a value that is not ambiguous. We've already seen that 6/4/2001 can be mistaken by people to mean different Dates (although it is not mistaken by the Database). Therefore there are only two viable ways of producing a Date String which can be read in any LCID and* the database from the one format. The first way is to use the VBScript FormatDateTime function and select vbLongDate as the formatting option, which will produce a Date String that looks like this: Tuesday, April 10, 2001. Unfortunately this format cannot be converted by the Query Engine.

    The alternative format for a Date which can be read the same way in any LCID and can be interpreted by the Query Engine is dd-MMM-yyyy (where MMM is the three-letter abbreviation of the month). Because there is no VBScript function to convert a Date into this format we would need to write our own:

    Function MediumDate (str)
        Dim aDay
        Dim aMonth
        Dim aYear
        aDay = Day(str)
        aMonth = Monthname(Month(str),True)
        aYear = Year(str)
    	MediumDate = aDay & "-" & aMonth & "-" & aYear
    End Function

    (Be sure to read the technical docs for more information on the MonthName function, which is used in the above function to return the month's abbreviation.)

    Here's an example of the MediumDate function in action, showing how to use it to get rid of your Date problems:

    strSQLUpdate = "UPDATE tblOrders " & _
                   "SET fldDateOrdered = #" & MediumDate( Date() ) & "# " & _
                   "WHERE fldStatus = open ; "

    Regardless of your LCID or your Server's settings the following string will be sent to the Query Engine:

    "UPDATE tblOrders SET fldDateOrdered = #10-Apr-2001# WHERE fldStatus = open ;"

    The secret to why this works is that the Database understands how to read the dd-MMM-yyyy format, and, as you can plainly see, 6-Jun-2001 will always be the 6th day of June 2001, no matter how you unpack it.

    I hope that you enjoyed this article. If you have any questions or comments be sure to provide me with any feedback!

    Happy Programming

  • By Darren Neimke

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