date help

can someone help me insert the date into mysql database i can't figure it out :S


what do i need to have the field set as? i want the date and time inserted.. do i set default as anything?


<cfif what is '1'>
<form action="test.cfm?what=2" method="post">
<input type="text" name="date" value="now()">
<input type="submit">

</cfif>



<cfif what is '2'>
test to see if its showing date
<cfoutput>#form.date#</cfoutput>
</cfif>

it just shows now()

Use now() in your insert.

It is MySQL, not HTML or CF.

Dude, I'm all for helping people, but come on now, did you even try to debug this? This is a no-brainer!

<input type="text" name="date" value="#now()#">

I'm worried that by helping people with "problems" like this that they're going to start to depend on the forum when they should be developing their debugging skills.

ok now it says #now()# instead of now()

wdn2000 - thanks i thought it had to be defined in the form.. does it matter what the feild is set as? because when i insert it as text it works fine, but when i do it as datetime it shows: 0000-00-00 00:00:00

or maybe i can leave it as text, would i be able to sort it by date then if the field is text? :P

Ok Charmed, I'm really not trying to sound like a jerk, so let me back off a bit.

Let's look at the problem you are experiencing. First you said that when you output the variable on the form's action page, all that comes out is "now()". The first thing you should do is a <cfdump var="#form#"><cfabort> to see if that is the value that is coming into the page in the FORM scope.

The dump would tell you that "now()" is coming in from the form, so that should tell you that the problem must be in the form. If you go look at the form, the value of that input field is "now()". Hopefully you can see that this is what the problem is, that you are actually submitting the text string "now()" instead of using a ColdFusion variable?

So I told you to replace it with "#now()#". And you come back and tell me that when you output it on the page now you get the text string "#now()#".

So think about this for a second and you tell me what the problem is. I'll give you a hint: it is almost unbelievably easy.

This would be better answered in the MySQL forum. I'm at work in a Windows shop so I don't have my MySQL reference handy.

Personally, if I'm in a *NIX environment I use the integer timestamp and convert it to human-readable for display only.

-----

Just checked my CF ref and it shows now() as a CF date/time specifier... It could be there's something different about the way CF formats date/time and MySQL expects it. But, kiteless example of #now()# should work to display in the form. This introduces other possibilities for problems but that's probably better left for another day.

wdn, I think you're digging in the wrong direction. He's not submitting the a date from his form, he's just submitting a string. All he needs to do is use the CF variable #now()# and wrap it in <cfoutput> like this:

<cfif what is '1'>
<cfoutput>
<form action="test.cfm?what=2" method="post">
<input type="text" name="date" value="#now()#">
<input type="submit">
<cfoutput>
</cfif>

I was going by the first thing said:
can someone help me insert the date into mysql database

There is a now() in MYSQL so I was going that way. If all that needs to be done is the current time is inserted, the user doesn't have to see any of it....

Yep, I think charmed was also assuming it was some kind of date issue with MySQL and missing the fact that he wasn't passing a date at all.

And charmed, once you have the date using now() you may also need to use createODBCDate() on that value to get it into an ODBC format that the database will accept. Some databases will take the format that now() creates, others want a special ODBC format for the date. Give the value from #now()# a try and if that doesn't work try the ODBC formatting function.

Or of course you could do as wdn is suggesting, and drop the date completely from the form. Just use MySQL's now() function and use it in the query.

The real root of the issue isn't whether you pass the date from the form or use the SQL now() function inside the insert statement. The issue is the train of thought that we must go through when we encounter a problem and need to trace it back to the cause.