Urgent !! Date/Time Insert

[Nomad2]

This is my current platform; Oracle as database, Sun Solaris as OS and CF4.0 for Solaris


I want to insert a data & time value but does not seem to work. Here is a snippet of the code.


<cfset yr = year(now())>
<cfset mt = month(now())>
<cfset dy = day(now())>
<cfset hr = hour(now())>
<cfset mn = minute(now())>
<cfset se = second(now())>

<cfset currentDateTime = createDateTime(yr, mt, dy, hr , mn, se)>

<cfquery name=&quot;qInsert&quot; datasource=&quot;test&quot;>
insert into trans_table (productid,bid_date)
values(3,#currrentDateTime#)
</cfquery>

The errors says invalid character, if I put a single quote between #currrentDateTime# is says error in comma ..

However, if I try inserting in an Access database no error will occur and everthing is fine.

I have even tried using #CreateODBCDateTime(NOW())# but does not work in Oracle but success in Access.

Alos I think the database & CF connection is by native rather than ODBC

 

[perichazhi]

Dear friend,

You need not explicitly store the date-time in oracle.
Oracle automatically stores the time of insertion or update.
If you want to retrieve the time, user the combimnation of to_char - to_date function.

Insert Command:

<cfset today=#Dateformat(Now(),&quot;dd-mmm-yyyy&quot#>

<cfquery name=&quot;qInsert&quot; datasource=&quot;test&quot;>
insert into trans_table (productid,bid_date)
values(3,#today#)
</cfquery>

OR
<cfquery name=&quot;qInsert&quot; datasource=&quot;test&quot;>
insert into trans_table (productid,bid_date)
values(3,SYSDATE)
</cfquery>


....

While querying

<cfquery datasource=&quot;test&quot; name=&quot;qgetdatetime&quot;>
select to_char(bid_date,&quot;hh:mm:ss&quot mytime from trans_table
</cfquery>


perichazhi

[sig][/sig]