I want to insert the current date and time into SQL when adding to table

I am trying to capture the date and time when data is entered into my SQL table.  This is what I currently have and it is not working.

<cfquery datasource="btadb" name="addcall">
insert into support
(accountnbr, calldate, contactid, callbackdate, subject, author, status, topicid, releaseid) values
(#form.account#, <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">, #form.contactid#, '#form.callbackdate#', '#form.subject#',
 #form.author#, 'open', #form.topic#, #form.release#)
</cfquery>


I am getting the error on this part

<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">

What am I doing wrong?
swartoutAsked:
Who is Participating?
 
hartConnect With a Mentor Commented:
i meant in ur sql database

in the design of the table

for the field calldate
u have default value option given, just put getdate() in their and everything will work fine.

what it does is the value of that field will always be the current date time or now()..
so every new record will automatically have the current date time

and also remove that field [calldate] from the insert statement  [cfcode]

Regards
Hart


0
 
hartCommented:
if u want current date time to be there in the field..
why not assign a default value in the field itself.

give it as getdate() as tehe default value.
and then u don't have to worry about the now()..
also the datatype should be datetime

Regards
Hart
0
 
swartoutAuthor Commented:
Can you please explain further, I do not understand what you mean.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anandkpCommented:
r u sure the error is on date ... & not on the other fields ?

can u paste teh error u got & also paste the output of ur query
i have a feeling it cld be the other data in ur query as well

try this query - just make sure u have the right datatypes in the following query for all the queryparams

<CFQUERY DATASOURCE="btadb" NAME="addcall">
      insert into support
      (accountnbr, calldate, contactid, callbackdate, subject, author, status, topicid, releaseid) values
      (
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.account#">,
      <CFQUERYPARAM VALUE="#now()#" CFSQLTYPE="cf_sql_timestamp">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#form.contactid#">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_date" VALUE="#form.callbackdate#">
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.subject#">
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.author#">'open',
      <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" MAXLENGTH="" NULL="No" VALUE="#form.topic#">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" MAXLENGTH="" NULL="No" VALUE="#form.release#">)
</CFQUERY>

K'Rgds
Anand
0
 
kjuliffCommented:
Don't double quote the date on insert.
0
 
swartoutAuthor Commented:
I tried what Hart suggested and it worked.  Thanks for all of the input.
0
 
hartCommented:
:-)

Regards
Hart
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.