davesabine.com

Hey Bartender, Make it a Double!

Working with dates in ASP/VBScript makes me want to bludgeon my skull with a blunt object. I've read all the articles and searched the internet for wisdom, but most documentation stops short of providing any real answer. Especially in multi-lingual environments, it seems there's just no option but to tie a bag of rocks around one's ankle and throw one's self into the river...or...perhaps the bartender has a solution?

I really mean this: -- avoid using dates whenever possible! Instead, use a data-type that VBScript will (almost) never screw up: the "DOUBLE". Read on to hear the long story...

The Tools -- Choose Wisely

The good folks at Microsoft provided VBScript with some built-in functions to help manage dates, numbers, currencies, and other data types. If you are reading this article, it's because you've realized that those built-in functions appear to be fundamentally flawed and you're considering throwing your forehead into the wall.

I'll try to keep this article focused on date/time functions -- because in my experience they are the most problematic and the solution is amazingly simple. VBScript includes the following functions which are intended to manage date/time data:

IsDate()

This is supposed to help you determine if a variable is of type "Date". It's inherently flawed and its output can't be trusted.

CDate()

Intended to convert a variable of type "(something)" to a variable of type "Date" but it can lie and is sometimes lazy. You must not trust it.

Second(), Minute(), Hour(), Day(), Weekday(), WeekdayName(), Month(), MonthName(), Year(), Date(), Time(), Now()

These produce -- supposedly -- a value of type "Date" based on the system's current date/time. I recommend you ignore all of them except "Now()"!

DateAdd(), DateDiff(), DatePart(), DateSerial(), DateValue(), TimeSerial(), TimeValue().

More date manipulation. Frankly, I've found only DateAdd() and DateDiff() to be of any real help and the others can be avoided altogether.

FormatDateTime()

A great function! But it has to be treated carefully.

What do you mean, "flawed"?

I mean that VBScript is not a strongly-typed language -- it's loose and happily changes our sloppy variables from strings to dates to numbers almost automatically. There are implicit data-type conversions happening all the time -- sometimes regardless of how careful a programmer tries to be. At the same time however, the built-in functions above only work reliably when the arguments are a predictable data-type; they'll break or produce unexpected results if they must perform implicit data-type conversion. That's the tragic flaw and there's no method to enforce strong data-types. (ASP.NET has "option strict" which allows a programmer to enforce strong-types and avoid such headaches.)

Do These Functions Work?

Yes and no. As far as I can tell, these functions work well if your application resides and operates in a very cohesive and structured environment. If your database, your application and your web server all reside on a single computer (or multiple computers with identical Regional Settings) then you should feel free to use all the above tools readily. But if your system's Regional Settings change or if you utilize VBScript's "SetLocale()" function or alter the ".CodePage" or ".LCID" properties of ASP's "Response" object, then you should expect problems and the above functions aren't as reliable as Microsoft would like you to believe. The solution is CDbl(), more on that later...

Multi-Lingual Settings

The problems inherent in the functions outlined above are amplified in multi-lingual situations. Imagine the following scenario:

• VBScript is decidedly a "US English" language.

• Perhaps your ASP application will be hosted on a web server in France so functions like CBool() produce "VRAI/FAUX" instead of "TRUE/FALSE" -- I didn't know that until my app broke on a server in France!

• The HTML pages will be written entirely in Turkish -- but the client wants to display currencies and dates in "British Pounds" and "en-gb" instead of Euro or the Turkish Lira.

• Perhaps the application will connect to a SQL database hosted in España.

• And perhaps the users of the application will be scattered around the globe and speak all 6800 known languages and they don't want to have to *think* about whether "DD" comes before or after the "MM" and whether "YYYY" should be abbreviated to just "YY" and separated with a "/ - : ." and so on.

I'm sure by now you understand the depth of the problem and you're starting to wonder if you can afford the bartender's tab.

The Problems

Is a "Date" Really a Date?

The table below demonstrates how the date functions usually operate just fine. Basically I will assigned a VBScript variable a value of "Now()" and then pass that information through other date-related functions. The script engine will correctly interpret the value as a "Date" data-type even in various locales.

All is well! It all checks out and there are no apparent problems. Below is the same set of tests, but this time I'll define the value of our variable manually:

Again...all is well! But only because I explicitly cast the variable as a "Date" while in the original locale. I had set the locale to "en-ca" and gave the script a valid Canadian date/time value. Here's what happens if I do not cast the value using CDate():

The problem is obvious I hope:

• The script engine switched the month and day around when the locale was reset to one which isn't consistent with the original input. Actually...it didn't switch them around but we hoped it would! Like, why didn't Microsoft realize that what we meant was the 3^rd of May!? -- just kidding. This is truly my fault, not Microsoft's. This of course doesn't happen if the day is greater than 12 (because the script engine surely knows that there are only 12 months and it will adjust for our laziness for dates like: "30/12/2006" or "12/30/2006").

• And no errors occurred! The "IsDate()" function and all others worked just fine and the script engine was able to implicitly convert the string value to a date value -- but it's clearly the wrong date! (Imagine explaining that to your CEO when all transactions posted in the first 12 days of every month are potentially wrong).

To conclude, "yes, a date is always a date" in the VBScript environment. The built-in functions are great and work very well...but only if you know with absolute certainty that your date begins as date and you can predict the Regional Settings and LCID/locale currently in use. But realistically it's ridiculous to rely on that kind of presumption -- you just can't be certain that every web server running your application will have identical Regional Settings.

One rule of thumb is demonstrated clearly above: Never trust implicit data-type conversion in the ASP/VBScript environment. Sometimes it's rather polite that Microsoft will automatically convert a number to a boolean, or a string to a date...but mostly it's just bad practice and should be avoided. Always define the data-type explicitly so that you can rely on a string being a string, a number being a number, and a date being a date. You can see that by casting the variable as a date early in example #2 prevented the problem experienced in #3.

But the problem doesn't stop there...

What About User Input?

This almost always means that you'll be receiving data from ASP's "Request" object: usually either the "Request.form" for "Request.querystring" methods.

That means two things:

• The incoming data is either "String" data-type or an "IStringList" -- but always a string-like thing. Your goal should be to immediately validate, test, and convert the incoming value to a suitable data-type so that later in the code you can trust the type of data you're dealing with.

• And the incoming data is totally unpredictable! Instead of inputting a date a user might tell you how they like their eggs! So: validate, test, then immediately convert the incoming value to a suitable data-type.

Moreover, if your users are scattered across the globe like mine are, then even the most careful input can be misinterpreted: Is February 1^st really 01/02? or 02/01? or 01-02? or 02-01? or 1:2?

What About Information From a Database?

This almost always means that you'll be receiving data from an ADO recordset.

That means two things:

• The incoming data is a "Field" data-type -- it's neither a number nor string nor date but a "Field"! Your goal should be to immediately convert the incoming value to a suitable data-type so that you can reliably pass the variable around your code without worrying about implicit type conversions.

• And the incoming data is predictable. You'll be able to trust that ADO is providing you with valid data (usually something you can easily test as NULL, or convert to a string/number/date/currency, etc.) This is wonderful news!

But again, if your application is running on systems configured with unpredictable Regional Settings, then your MS Access or MSSQL database may respond with values that your code isn't prepared for (unless of course you're prepared and then why are you reading this?)

The Solution

I really mean this: -- avoid the "date" data-type whenever possible. Use a data-type that VBScript will (almost) never screw up: the "DOUBLE". This policy isn't without problems, but I'd much rather manage numbers that bizarre values like "09-04-01 04.12.00" and have to guess what language or country that relates to or which of those numbers is the month?!

It's really quite simple:

<%
dim dblNow
    dblNow = CDbl(Now())
%>

VBScript's date functions will all work as well and better with a "double" rather than with a "date". As well, the locale or LCID can be changed with liberty and without breaking the date functions when you do need them.

Is a "Date" really a Double?

Yes. A date can be represented as a "Double" number (that's a floating-point decimal number). The VBScript engine is quite happy to store, calculate, and display dates using numbers instead of bizarre strings of letters/numbers/weird-characters like "14/1/1997 3:34:00 PM". In fact, the script engine uses numeric representations already and doesn't bother about the letters and digits that we like to see on our calendars until it has to print the date to a page.

For example, the following numbers can be easily transformed into dates and vice-versa:

FormatDateTime(0,1): December 30, 1899

Zero is considered the beginning of time -- the VBScript engine assumes that midnight on December 30, 1899 was the beginning of time.

CDbl(Now()): 38837.7364467593

"Now()" can be represented as a decimal number. The numbers preceding the decimal indicate the number of days since December 30, 1899. The digits following the decimal is a fraction representing the portion of today that has passed (according to the code above, 73.645% of today has passed).

FormatDateTime(38837.7364467593,1): April 30, 2006

You can see that we can plug the decimal number back into the FormatDateTime() function (the function then implicitly converts the data to a date/string).

FormatDateTime(DateAdd("D",38837.7364467593,1),1): May 1, 2006

You can see above that the DateAdd() function still enables us to perform math with the "Double". I've added one (1) day ("D") and the script engine responds as you'd expect with tomorrow's date. All the other built-in functions work too, except that "IsDate()" will prove False.

FormatDateTime((38837.7364467593 + 1),1): May 1, 2006

Alternatively, I can add one (1) to our "Double" number and get the same answer.

Wednesday, May 3^rd in every locale!

What About User Input?

If you forge ahead with my solution, you'll still find that this is the most difficult aspect of an application. Users just aren't as predictable as we'd like but the following advice will help:

• Give your users an example to follow. Your forms should never look like this:

  Please enter today's date:

  But instead:

  Please enter today's date: Example: 31/12/1899 2:24:00 AM

• Or better yet, you might provide a calendar-control of some type where the user can point-n-click and you can control the hidden input.

• Then immediately validate, test, and convert the user's input to a "Double" when you retrieve the information from "Request.form" or "Request.querystring". I use a function for this purpose:

  <%
dim dblDate
    dblDate = DblDate(request.form("TodaysDate"))

FUNCTION DblDate(ByVal variantDate)

'the argument might be any of the following data types:
' Date (if a valid date is coming from a database or elsewhere in the application)
' Double (if the variable has already been converted to a double)
' IStringList (if data such as "123456,123456789" is coming from elsewhere in the application)
' Field (in which case it likely contains a double or a string formatted like a double or an IStringList by the database)
' String like "10/10/2006 12:00:00 AM"

dim dblDateValue
dim dblTimeValue

on error resume next

    IF (TypeName(variantDate) = "Date") OR (TypeName(variantDate) = "IStringList") THEN
    'Then it's a date and the CDbl conversion will work
    dblDateValue = CDbl(Replace(variantDate,",","."))

    ELSEIF TypeName(variantDate) = "Double" THEN
    'Then it's already a double...no conversion necessary
    dblDateValue = variantDate

    ELSE
    'Then it's either a field or a string and things get tricky
    dblDateValue = CDbl(CDate(variantDate))

        IF err.Number <> 0 THEN
        'If the logic above caused an error, then the CDate() conversion wasn't successful.
        'which means that the incoming value isn't a known date format - maybe it's a number
        'or maybe it's a string-type date in a different locale or language
        'or maybe it's a recipe for eggs benedict...let's try one last time:
        err.Clear
        dblDateValue = CDbl(variantDate)
        END IF

    END IF

    IF err.Number <> 0 THEN
    'if there's still an error, then the incoming value cannot be interpreted as a date or number
    'we can't say we didn't try...but so to not break the rest of the code we'll assign a value of zero
    'and return a valid "Double" anyways!
    dblDateValue = CDbl(0)
    END IF

err.Clear
on error GoTo 0

'and now we can return a valid double
DblDate = dblDateValue

END FUNCTION
%>

• And after the user's input is successfully validated and converted to a "Double", you can feel comfortable passing that variable around in your code. You can perform math with it, display it on your page, dump it into a database, etc. and because it's a number, not a date, changing the locale or LCID won't have any unexpected results.

Well, I'm glad that's settled!

But what's the "IStringList" exactly?

The only problem I have encountered using "Double" instead of "Date" data types is that VBScript will format a "Double" with a comma instead of a period -- and the reason isn't obvious.

The following code:

<%
SetLocale("en-ca")
response.write(CDbl(1234.5678))
response.write("<br />")

SetLocale("es-es")
response.write(CDbl(1234.5678))
response.write("<br />")
%>

Will output this:

1234.5678
1234.5678

But...(you can start reaching for a brick to throw at your forehead)...

This:

<%
SetLocale("en-ca")
response.write(CDbl(1234.5678) &"<br />")

SetLocale("es-es")
response.write(CDbl(1234.5678) &"<br />")
%>

...which you'd think is fundamentally the same, will output this:

1234.5678
1234,5678 (Note the comma!)

So, what's different between the first and second examples?

1. In the first example above, the number was converted to a double, then passed to the "Response" object which implicitly converted the data to a "String" when the ASP engine wrote to the output buffer.

2. In the second example, the number was converted to a double, then immediately/implicitly to a string by the VBScript engine when we concatenated the
   tag!

What can we learn by this? We can learn that the "Response" object is a perfectly dumb object -- it just throws everything it recieves to the output buffer -- with "ToString" character conversion. We can also learn that VBScript formats a "Double" with a comma in the "Español (España)" locale. When in Spain, if we try to convert from a string to a double, then we have to expect that the incoming data-type might be an "IStringList": a comma-separated list as in "1234 , 5678". This will occur if you retrieve the value from the "request.querystring" or "request.form" methods.

Assume for a moment that a querystring key named "spanishDouble" has a value of "1234,5678" as in: thisPage.asp?spanishDouble=1234,5678

<%
SetLocale("es-es")

' This will be "IStringList"
response.write(TypeName(request.querystring("spanishDouble")))
response.write("<br />")

dim varSpanishDouble
' But it will be implicitly cast as a "String" here.
    varSpanishDouble = request.querystring("spanishDouble")

' Proof!
response.write(TypeName(varSpanishDouble))
response.write("<br />")

' And, the CDbl() function works.
response.write(FormatDateTime(CDbl(varSpanishDouble),1))
response.write("<br />")
%>

Will output this:

IStringList
Empty
sábado, 30 de diciembre de 1899

Hmm...

Now let's throw the querystring immediately into the CDbl() without assigning it first to a variable.

<%
SetLocale("es-es")

' This will be "IStringList"
response.write(TypeName(request.querystring("spanishDouble")))
response.write("<br />")

response.write(FormatDateTime(CDbl(request.querystring("spanishDouble")),1))
response.write("<br />")
%>

Will output this:

IStringList
sábado, 30 de diciembre de 1899

Now to stir things up (you can reach for that brick again)...if the incoming querystring value (in the Spanish example) has a period instead of a comma, then the "CDbl()" will strip the period entirely (instead of as you'd hope it should convert the period to a comma) and the conversion will be incorrect.

So, the number "1234.5678" becomes "12345678" and an error occurs because that's not a valid date (that's 33 thousand years past December 30, 1899).

So the rule is: "Double" data-types in some locales will be formatted with a comma, not with a decimal-period. Once you understand that single exception, then using "Double" types is a lot easier than using "Date" types. Using "Date" types immediately introduces as many exceptions as there are rules!

What about Information From a Database?

Imagine now that:

• your ASP pages are running on a web server in France, but your SQL database is on a server in España.

• Or, (perhaps more commonly), perhaps you use a MS Access database on the same computer or network as your web server but your ASP scripts use a LCID that differs from the system's Regional Settings.

Both of these scenarios are common, and both pose the same problem: the database is liable to receive and respond with data that is formatted differently than your script engine. I can think of a few thousands ways that otherwise great code can break...they all boil down to data-type mismatch or syntax errors in your UPDATE or INSERT queries.

Enter the "Double"! I store dates as dates in a database -- I mean, why not? But that's as far as it goes...as I retrieve information from a database I convert it to a "Double" and the data stays that way until I output it to a page with the FormatDateTime() function (whereby it gets implicitly converted to a string) or until I purposely convert it to a field or string to put back into the database! Consider the following:

To retrieve data from a database:

MS Access

set objRecordsetObject = objConnectionObject.Execute("SELECT CDbl(datefield) AS dblDateField FROM table;")

SQL

set objRecordsetObject = objConnectionObject.Execute("SELECT convert(float,datefield)+2 AS dblDateField FROM table;")

Note, the "+2" is to accommodate the fact that MSSQL considers January, 1, 1900 to be the beginning of time (zero) while VBScript considers December 30, 1899 to be beginning of time (zero). I know, I know...you're thinking "that's dumb!"

These example queries are important for two reasons:

• First, the database will perform the conversion from date to double. That's great considering the alternative. If we instead retreive a "date" from the database and ask the VBScript engine to perform the conversion then we run the risk that the two environments may interpret the values differently. But we can be assured that the database will produce a double/float that is exactly equal to the date every time.

• Secondly, because there's no mystery at all about what the value might look like when VBScript gets its hands on it. We know that it will a floating-point decimal-digit number; it won't be NULL! and it won't be a bizarre string of alpha-numeric-babble.

  We can immediately do this and life is good:

  dim dblDate
    dblDate = CDbl(objRecordsetObject("dblDateField"))


Then, because we have a value that we know and trust is a valid "Double", we can manipulate it, compare and test it, and write it out to a page -- do whatever you want with it. You'll find yourself as comfortable using these variables as you are with integers and text.

Example...

    IF dblDate = 0 THEN
    response.write("It's the beginning of time!<br />")
    ELSE
    SetLocale("es-es")
    response.write("The date in Spanish is: "& FormatDateTime(dblDate,1) &"<br />")
    SetLocale("fr-ca")
    response.write("The date in Canada is: "& FormatDateTime(dblDate,1) &"<br />")
    response.write("The next day is "& MonthName(dblDate+1) &", "& Year(dblDate+1) &"<br />")
    SetLocale("tr")
    response.write("A month earlier in Turkey: "& FormatDateTime(DateAdd("M",dblDate,-1)) &"<br />")
    END IF

And Updating or Inserting into a Database

Again, when passing a "double" to a database, it's best to let the database perform the number-to-date conversion because it doesn't get confused. The various tools, ASP, VBScript, and your data provider get confused with dates because they each interpret dates according to their own unique rules -- but they never confuse numbers.

In the example below assume that the "datefield" is a database column of type "date" (date/time or smalldate, whatever you happen to be using.)

MS Access

objConnectionObject.Execute("INSERT INTO table (datefield) VALUES ("& Replace(dblDate,",",".") &");")

Yes, if this string were written with constants, then it might look like this:

"INSERT INTO table (datefield) VALUES (1234.5678);"

And you'll say, "but doesn't that cause a type mismatch?". Nope. It doesn't.

And note that the VBScript "Replace()" function can be used for good measure to ensure that those Spanish doubles (with commas) won't cause a syntax error in the query.

SQL

And the same query (above) will work with a SQL provider.

Conclusion

Make it a "Double".