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.
Next: ¿Que Horas Son? >>
More MySQL Articles
More By Justin Cook
/ 41 
