
convert vbscript now() into mysql DateTime format
Does anyone have a function that can do this?<BR><BR>I already have one for converting it to the MySQL date format, but not DateTime<BR><BR>This is the Date function:<BR><BR>Function fnConvertToMySQLDate(varDate)<BR> Dim sDay,sMonth<BR> if day(varDate) < 10 then<BR> sDay = "0" & day(varDate)<BR> else<BR> sDay = day(varDate)<BR> end if <BR> <BR> if month(varDate) < 10 then<BR> sMonth = "0" & month(varDate)<BR> else<BR> sMonth = month(varDate)<BR> end if<BR> fnConvertToMySQLDate = year(varDate) & sMonth & sDay<BR>End Function

Yeah, I should make this an FAQ...
It's called ISODate (or at least that's what I have called it).<BR><BR>Function ISODate( dt )<BR> If Not IsDate( dt ) Then<BR> ISODate = "NULL"<BR> Else<BR> dt = CDate( dt ) ' safety<BR> ISODate = "'" & Year(dt) & "" & Month(t) & "" & Day(dt) & "'"<BR> End If<BR>End Function<BR><BR>You do *not* need to pad the month and day with zeroes to make them 2 characters long. MySQL is happy with 200517 honest. Oracle, on the other hand, *does* require the padding. So you'd want<BR> ISODate = "'" & Year(dt) & "" & Right("0" & Month(t),2) & "" & Right("0" & Day(dt),2) & "'"<BR><BR>Notice that I put the '...' around the date in the function. That's so you *can* pass nondates to the function and have them get translated to NULL and so the SQL makes sense.<BR><BR>e.g.,<BR><BR>SQL = "INSERT INTO foo ( someDate ) VALUES( " & ISODate(whenever) & ")"<BR><BR>

OK, it's half way there  but I've solve
Thanks Bill,<BR><BR>Although that only gives me the date in the MySQL format  and not the current time.<BR><BR>So, for anyone who has this query in the future, here's the answer (which is really just a logical extension of the function I was currently using..)<BR><BR>Function fnConvertToMySQLDate(varDate)<BR> Dim sDay,sMonth<BR> If day(varDate) < 10 Then<BR> sDay = "0" & day(varDate)<BR> else<BR> sDay = day(varDate)<BR> end if <BR> If month(varDate) < 10 Then<BR> sMonth = "0" & month(varDate)<BR> Else<BR> sMonth = month(varDate)<BR> End If<BR> fnConvertToMySQLDate = year(varDate) & sMonth & sDay & " "& hour(varDate) & minute(varDate) & second(varDate)<BR>End Function

No, that does NOT work!
If the time is, for example, 9:03:17 AM then you will end up having<BR> 9317<BR>as your time string. But if the time is 9:31:07 AM then you will end up having<BR> 9317<BR>as your time string! Can you say "OOPS!"<BR><BR>You need to pad the hours, minutes, and seconds to 2 digits as well as the month and day if you aren't going to use delimiters.<BR><BR>*IF* you use delimiters (the "" that I used, but then ":" for the time) *THEN* you can allow single digits:<BR> 9:3:17<BR> 9:31:7<BR><BR>

correction to ISODate function
I know this thread is old but if you are trying to use it.... there was an error. Thanks also for this post, it was most helpful to me.
Function ISODate( dt )
If Not IsDate( dt ) Then
ISODate = "NULL"
Else
dt = CDate( dt ) ' safety
ISODate = "'" & Year(dt) & "" & Month(t) & "" & Day(dt) & "'"
End If
End Function
That should be
ISODate = "'" & Year(dt) & "" & Month(dt) & "" & Day(dt) & "'"
Thanks!

Oops...well, I was sloppy then and I'm still sloppy, but glad it helped you.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

