Error with Datetime

[kelani]

Simple insert causing problems.

Insert code is

<cfquery name=&quot;db_insert&quot; datasource=&quot;db26401a&quot; dbtype=&quot;ODBC&quot; username=&quot;us26401a&quot; password=&quot;dxm714&quot; dbserver=&quot;localhost&quot; dbname=&quot;db26401a&quot;>
INSERT INTO users (id, date, rate, data) VALUES ('', '#DateFormat(CreateODBCDatetime(form.date), &quot;yyyy/mm/dd HH:MM:SS&quot#', '#form.rate#', '#form.data#')
</cfquery>

In the page, I have this:

<input type=&quot;hidden&quot; name=&quot;date&quot; value=&quot;#DateFormat(Now(), &quot;yyyy/mm/dd HH:MM:SS&quot#&quot;>


Error I am getting is:

Parameter 1 of function CreateODBCDateTime which is now &quot;2000/12/17 HH:12:SS&quot; must be a date/time value

Is this input screwing things up? How come it's showing 12 for minutes every time?

Kelani

 

[bobbyr]

Do you need both the Date and Time formatted and stored? If so, you might try doing something like this:

<cfset CurrentTime = TimeFormat(Now(), &quot;hh:mm:ss&quot>
<cfset CurrentDate = DateFormat(Now(), &quot;yyyy/mm/dd&quot>
<cfset DateTime = CurrentDate &amp; CurrentTime>
<cfquery name=&quot;db_insert&quot; datasource=&quot;db26401a&quot;
dbtype=&quot;ODBC&quot; username=&quot;us26401a&quot;
password=&quot;dxm714&quot; dbserver=&quot;localhost&quot;
dbname=&quot;db26401a&quot;>
INSERT INTO users (id, date, rate, data) VALUES ('', '#DateTime#', '#form.rate#', '#form.data#')
</cfquery>

This way, you wouldn't even need the input from the form page.

If you don't need both the date AND time, you could do either:

<cfset CurrentTime = TimeFormat(Now(), &quot;hh:mm:ss&quot>
or
<cfset CurrentDate = DateFormat(Now(), &quot;yyyy/mm/dd&quot>
and then use the variable in your INSERT statement.

The problem you are having is that you are using a DateFormat Function on TIME. The DateFormat Function is interpreting your mm's in the time as the Month, which is why it is always 12. DateFormat doesn't know what the hh or ss is either.

If anybody else has a better way to do this, please feel free to inform us

Bobby R

 

[iza]

i thought the problem was that the date is actually formated twice : once in the hidden field and once in the query, and maybe formatting an already formatted date is confusing for the parser

 

[kelani]

Actually both work. Somehow, an extended search &amp; replace changed the table it was trying to insert into, which obviously confused the parser. I wasn't getting a 'field not found' error, so that was weird.

Cold Fusion error reporting is good, but far from perfect.

Thanks for the help.