ASP From the Trenches — Hey Bartender, Make It A Double!

REPRINT FROM MAY, 2003.

If you are lucky enough to never have to work on legacy code, leave this page.

Otherwise, stay for some tips when working with Classic ASP.

Working with dates in ASP/VBScript makes me want to bludgeon my skull with a blunt object. Especially in multi-lingual environments.

The Solution

I really mean this: avoid using dates whenever possible!

Instead, use a data-type that VBScript will (almost) never screw up: the “DOUBLE”.

The Tools – Choose Wisely

VBScript has 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 those built-in functions appear to be fundamentally flawed.

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 and back. There are implicit data-type conversions happening all the time – regardless of how careful a programmer tries to be.

And 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.

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). 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 reliable.

The solution is CDbl(), more on that later…

Multi-Lingual Settings

The problems are amplified in multi-lingual situations. Imagine the following scenario:

1. VBScript is decidedly a “US English” language.

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

3. 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.

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

5. 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.

Example Test #1

Let’s start with a value that we know is a date: “Now()”

<%
 SetLocale("en-ca")
 dim theDateVariable
 theDateVariable = Now()
%>

“Now()” at the time of this writing is: 03/May/2003 3:47:29 PM

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = May 3, 2003 'vbLongDate

SetLocale(“es-es”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = sábado, 03 de mayo de 2003 'vbLongDate

SetLocale(“en-us”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 5/3/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = Saturday, May 03, 2003
‘vbLongDate

SetLocale(“tr”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03.05.2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = 03 Mayis 2003 Cumartesi 'vbLongDate

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:

Example Test #2

I’ll set the variable to the 3rd of May and ensure that it’s a valid “Date” data-type by using the CDate() function…then I’ll pass it through the same tests as above.

This will work because I explicitly cast the variable as a “Date” while in the original locale. I set the locale to “en-ca” and give the script a valid Canadian date/time value.

<%
 SetLocale("en-ca")
 dim theDateVariable
 theDateVariable = CDate("03/05/2003 3:47:29 PM")
%>

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = May 3, 2003 'vbLongDate

SetLocale(“es-es”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = miércoles, 03 de mayo de 2003 'vbLongDate

SetLocale(“en-us”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 5/3/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = Wednesday, May 03, 2003 'vbLongDate

SetLocale(“tr”)

TypeName(theDateVariable) = Date
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03.05.2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = 03 Mayis 2003 Çarsamba 'vbLongDate

Ok, so where’s the problem?

Here’s what happens if I do not cast the value using CDate():

Example Test #3

I’ll set the variable to the 3rd of May again and this time rely on implicit data-type conversion…then I’ll pass it through the same tests as above and watch it break.

<%
 SetLocale("en-ca")
 dim theDateVariable
 theDateVariable = "03/05/2003 3:47:29 PM"
%>

TypeName(theDateVariable) = String 'Whoa!!!!!!!!!!!
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = May 3, 2003 'vbLongDate

SetLocale(“es-es”)

TypeName(theDateVariable) = String 'Whoa!!!!!!!!!!!
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03/05/2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = miércoles, 03 de mayo de 2003 'vbLongDate

SetLocale(“en-us”)

TypeName(theDateVariable) = String 'Whoa!!!!!!!!!!!
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 3/5/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(theDateVariable,1) = Sunday, March 05, 2003 'Whoa!!!!!!!!

SetLocale(“tr”)

TypeName(theDateVariable) = String 'Whoa!!!!!!!!!!!
IsDate(theDateVariable) = True
FormatDateTime(theDateVariable,0) = 03.05.2003 15:47:29 'vbGeneralDate
FormatDateTime(theDateVariable,1) = 03 Mayis 2003 Çarsamba 'vbLongDate

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 3rd of May!? – just kidding.

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/2003” or “12/30/2003”).

And no exceptions were thrown. 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 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. 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! So: validate, test, then immediately convert the incoming value to a suitable data-type.

If your users are scattered across the globe like mine are, then even the most careful input can be misinterpreted: Is February 1st 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.)

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.

The Solution

I really mean this: avoid using dates whenever possible!

This policy isn’t without problems, but I’d much rather manage numbers than 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 will 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.

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.

Example Test #4

This time, I’ll set the variable to the 3rd of May again and convert the data immediately to a “Double”…everything works except this time the “IsDate()” fails (which I expect because I’ve purposely changed the data-type to “Double”).

<%
 SetLocale("en-ca")
 dim dblDate
 dblDate = CDbl(CDate("03/05/2003 3:47:29 PM"))
%>

TypeName(dblDate) = Double 'THE WAY WE LIKE!!!!!!
IsDate(dblDate) = False 'AS EXPECTED!!!!!!
FormatDateTime(dblDate,0) = 03/05/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(dblDate,1) = May 3, 2003 'vbLongDate

SetLocale(“es-es”)

TypeName(dblDate) = Double 'THE WAY WE LIKE!!!!!!
IsDate(dblDate) = False 'AS EXPECTED!!!!!!
FormatDateTime(dblDate,0) = 03/05/2003 15:47:29 'vbGeneralDate
FormatDateTime(dblDate,1) = miércoles, 03 de mayo de 2003 'vbLongDate

SetLocale(“en-us”)

TypeName(dblDate) = Double 'THE WAY WE LIKE!!!!!!
IsDate(dblDate) = False 'AS EXPECTED!!!!!!
FormatDateTime(dblDate,0) = 5/3/2003 3:47:29 PM 'vbGeneralDate
FormatDateTime(dblDate,1) = Wednesday, May 03, 2003 'vbLongDate

SetLocale(“tr”)

TypeName(dblDate) = Double 'THE WAY WE LIKE!!!!!!
IsDate(dblDate) = False 'AS EXPECTED!!!!!!
FormatDateTime(dblDate,0) = 03.05.2003 15:47:29 'vbGeneralDate
FormatDateTime(dblDate,1) = 03 Mayis 2003 Çarsamba 'vbLongDate

Wednesday, May 3rd 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.

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/2003 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 pass 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…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?

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.

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…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:

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

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

Conclusion

Make it a “Double”.