Can't insert a date into mySQL datetime field

Hey.

I'm trying to do a simple insert into a mySQL datetime field. Actually, I've tried all the other date types and none of them work for me. Here's a test snippet ...

<code>
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=xxxx;uid=xxxx;password=xxxx"
set objRS=Server.CreateObject("ADODB.recordset")
objRS.CursorLocation=3
objRS.CursorType=3

sql = "INSERT INTO testTbl " &_
"SET date_time = '" & formatDateTime(now(),0) &"'"
objRs.Open sql,objConn
</code>

This doesn't work. I get nothing but zeros no matter what date type I use. But if I hard-code a date (e.g. 2004-01-27 12:00:00 AM), it works. Please help.

-Jorge

NOW() is a mysql function that you're wrapping in non-mysql code. You should just be able to insert NOW() without wrapping it in the formatDateTime() function.

dhouston,
Thanks for the quick reply. I had already tried that but I tried it agian anyway. Still doesn't work. Again I hard-coded the date and it worked fine. It's a mystery.

-J

try this (based on your first code)
sql = "INSERT INTO testTbl SET date_time = NOW()'"

OK. what in tarnation 's goin on?

Yes! that worked! I thought the NOW() had to be outside of the string. thank you X 3 man!

No, as NOW() is a mysql function, it has to be inside the string passed to mysql, where it is interpreted and converted to the current date.