CFQuery not saving time portion to Oracle Datetime field

Question

I am having a problem using a datetime field in an Oracle database with Coldfusion with and don't know how to solve it.

I have a table (Subject) which contains a datetime field ("MODIFIED"). If I look the date I obtain 04-JAN-17 for instance. If I do this query:

select to_char(MODIFIED, 'DD-MM-YYYY HH24:MI:SS') from Subject

It returns:

04-01-2017 09:57:43

I can retrieve this same result in ColdFusion.

I then try to copy that data to another table with the same structure if the "MODIFIED" value has changed. For doing that I retrieved the data in a first query and insert it into the new table. The data is retrieved correctly. Especially the date (I can see it in the ColdFusion script). However, the time of the date is lost. So for instance, I obtain 04-01-2017 00:00:00 instead of 04-01-2017 09:57:43.

Here is the relevant part of my script:

Main page:

<!-------- Get data -------->
<cfquery name="select_Subject_to_insert" datasource="#application.datasource#">
    SELECT CODE, MODIFIED, NAME FROM Subject 
</query>


<cfloop query="select_Subject_to_insert"> 
    <!-------- Create an object "Subject" -------->
    <cfscript>
        subject_to_insert = createObject("component", "Subject").init();
        subject_to_insert.id = -1;
        subject_to_insert.Code = select_Subject_to_insert.CODE;
        subject_to_insert.modified = select_Subject_to_insert.MODIFIED;
        subject_to_insert.name = select_Subject_to_insert.NAME;             
    </cfscript>
    <!-------- Call the function for saving data -------->
    saveSubject(subject_to_insert)
</cfloop>

Main component:

<!--- Function to save (update) Subject. --->
<cffunction name="saveSubject" returntype="string" access="remote">     
    <cfargument name="subject" required="yes" type="vo.Subject" />
    <cfset var timestp = createTimeStamp() />

    <!--- insert --->   
    <cfquery name="insertSubject" datasource="#application.datasource#" result="insertSubjectResult">
        insert into Subject (
            CODE,
            MODIFIED,
            NAME
            TIMESTAMP
        )values ( 
            <cfqueryparam value="#arguments.subject.CODE#" null="no" cfsqltype="cf_sql_varchar"/>,                                                  
            <cfqueryparam value="#arguments.subject.MODIFIED#" null="no" cfsqltype="cf_sql_date"/>,                         
            <cfqueryparam value="#arguments.subject.NAME#" null="no" cfsqltype="cf_sql_varchar"/>,                          
            <cfqueryparam value="#timestp#" null="no" cfsqltype="cf_sql_numeric"/>                          
        )
    </cfquery>

    ........................................

</cffunction>   

Could you please help me to understand why the time is not inserted correctly and solve the problem?

Answer 1

You want to use:

<cfqueryparam value="#arguments.subject.MODIFIED#" null="no" cfsqltype="cf_sql_timestamp"/>

The cf_sql_date type does not have a time component whereas cf_sql_timestamp does. You can check the compatability matrix in the documentation to see that cf_sql_timestamp corresponds to the Oracle DATE data type.

You can also simplify your page to get rid of the function call (assuming it is not doing extra processing):

<cfquery name="insertSubject" datasource="#application.datasource#" result="insertSubjectResult">
  insert into Subject (
    CODE,
    MODIFIED,
    NAME
    TIMESTAMP
  )
  SELECT code,
         modified,
         name,
         <cfqueryparam value=createTimeStamp() null="no" cfsqltype="cf_sql_numeric"/>
  FROM   subject
  -- WHERE some_condition
</cfquery>