ASP and MySQL are designed and distributed with completely different rationale, not to mention by two very different companies. But there is a good chance that you may want these two technologies for your dynamic application, in which case you need them to 'talk' to each other. One major obstacle to this could be that they differ in their handling of dates and times. This article will show you two functions you can implement to allow them to communicate peacefully, without trying to re-configure date/time formatting in either.
Two Lessons in ASP and MySQL - ¿Que Horas Son? (Page 3 of 4 )
In case you're wondering whether the author of this article has suddenly gone nuts and is typing upside-down question marks; it's OK, it's just Spanish for 'what time is it?'. I just use that to illustrate that different people/systems have different ways to say the same thing. As we've established, MySQL and ASP handle time formats differently. ASP might say 4:29:09 PM, but MySQL would say the same time is really 16:29:09.
My goal was to just create a function similar to the previous one, but realized I was up against one further obstacle. I can use a calendar to force users to specify a specific date, and input it in a specific format. But when you ask a user to specify a time (perhaps the start time for an event), it's a little harder to enforce a specific format. This is especially so if you're pulling the data from another application that doesn't have the same constraints as yours. Some might say 4 pm, some 4:00pm, and so on. Here's what I've come up with to handle this:
'==================== function mysqlTime( t, dir ) '====================
dim strSuffix, arTime, i, x
t = trim( Lcase( t ) ) if inStr( t, "pm" ) > 0 OR inStr( t, "am" ) > 0 then strSuffix = right( t, 2 ) t = left( t, inStr( t, strSuffix ) -2 ) t = trim( t ) end if
What I've done here is check for an 'am' or 'pm' suffix. This gets trimmed off and stored away for later reference.
for i = 1 to len( t ) x = mid( t, i, 1 ) if not isReallyNumeric( x ) and x <> ":" then t = replace( t, x, "" ) next
This little chunk of code goes through the trimmed string, and removes any non-integers. It also leaves colons in place. After all, who'd want their colon removed by a ferocious little ASP function? No, we need it, as you'll see shortly. What this is good for, is removing the o'clocks, or any other strange things people are entering that they shouldn't. The isReallyNumeric() function comes from this article.
arTime = split( t, ":" ) t = "" for i = 0 to 2 if uBound( arTime ) < i then redim preserve arTime( i )
if i = 0 then if dir = 1 then if strSuffix = "pm" and cInt( arTime( i ) ) < 12 then arTime( i ) = cInt( arTime( i ) ) + 12 end if else if cInt( arTime( i ) ) > 12 then arTime( i ) = cInt( arTime( i ) ) - 12 strSuffix = "PM" else strSuffix = "AM" end if end if end if
do until len( arTime( i ) ) = 2 arTime( i ) = "0" & arTime( i ) loop
t = t & arTime( i ) if i < 2 then t = t & ":" next
So here in this fairly illegible piece of script, I've split the string into an array, and done three loops, one for the hour, the minutes, and the seconds. For each one, it is checked whether there are the full two digits, and if not, a zero is appended. The direction is checked to see if we want 24-hour format, or 12-hour format. Whatever information is missing becomes a zero, so 8, 8:00, and 8:00:00 all produce 8:00:00.
arTime = null if dir = 2 then t = t & " " & strSuffix 'debug( t ) mysqlTime = t end function
This is the cleanup and return bit. The suffix is appended is necessary. The debug() call is also from the URL I pointed you to before, which you'll find quite handy for getting information about variables before you try to work with them.