Home arrow MySQL arrow Page 3 - Two Lessons in ASP and MySQL

Two Lessons in ASP and MySQL

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.

Author Info:
By: Justin Cook
Rating: 4 stars4 stars4 stars4 stars4 stars / 77
May 10, 2004
  1. · Two Lessons in ASP and MySQL
  2. · It's a Date!
  3. · ¿Que Horas Son?
  4. · Conclusion

print this article

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, "" )

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
    if cInt( arTime( i ) ) > 12 then
     arTime( i ) = cInt( arTime( i ) ) - 12
     strSuffix   = "PM"
      strSuffix   = "AM"
    end if
   end if
  end if
  do until len( arTime( i ) ) = 2
   arTime( i ) = "0" & arTime( i )
  t = t & arTime( i )
  if i < 2 then t = t & ":"

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.

blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials