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.
Next: Conclusion >>
More MySQL Articles
More By Justin Cook
/ 41 
