Home arrow MySQL arrow Page 2 - 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 - It's a Date!
(Page 2 of 4 )

If you're still a little unclear as to what I'm talking about, I'll explain. ASP handles dates in the following fashion: MM/DD/YYYY. MySQL would rather store a date like this: YYYY-MM-DD. So when MySQL receives a command to insert '2/22/2004', it throws up its hands in despair, and substitutes a '0000-00-00'. Ahh, much better, no null value. But no date either, so now we're throwing our hands or keyboards in despair. Here's the function I made to handle the conversion we need:

function mysqlDate( d, dir )
 'if not isDate( d ) then call errorMessage( d & " is not a date " )
 'if not isDate( d ) then exit function
 if not isDate( d ) then d = Date()

The first three lines of code are all optional, but I do suggest that you use one of them (and only one will work of course, so don't try to use two!!). This are basic error checking. The first line calls a subroutine that I explained in the article Easy Error Management. Basically it's just a pleasant way of screaming that there's a serious problem, in this case, trying to perform date formatting on a non-date.  The other two options are to just exit the function, or replace 'd' with the current date value. You can choose whichever is most applicable in your situation, and uncomment that line.

 dim strNewDate
 select case dir

Now we're making use of the 'dir' parameter. This is just a numeric switch, indicating where the date is going to be used, and therefore how it needs to be formatted. Let's go through them; there's only two after all...

  case 1 '=== store in db
  strNewDate = year( d ) & "-" & month( d ) & "-" & day( d )

So, all I've done is just taken the date, dissected it, and pieced it together in the proper format, placing the dashes in their proper places. Not too difficult. But how do we handle a date we receive from MySQL? Surely we'll have to go through some trouble to extract the proper pieces of the string around the dashes, right? Well, that's the approach I had originally taken, and there were about 5 additional lines of code in this function. But then by some serendipitous event which I have long forgotten, I realized that ASP can read the MySQL YYYY-MM-DD format. WOW! But since I wanted to have the date in native VBScript format, I mimicked the previous line, and cut out all the unnecessary string chopping:

  case 2 '=== use with asp
   strNewDate = month( d )& "/"  & day( d ) & "/" & year( d )
 end select

The last thing I did was convert the resulting string to a date.

 strNewDate = cDate( strNewDate )
 mysqlDate = strNewDate
end function

That's all it took. I guess you could expand on this function to handle any date formatting out there, if there are any others. Now I'll show you the time formatter.

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