ColdFusion: Insert Date Time in One Variable Into Database Oracle10

Question

I have stuck with this problem for one hour. I am using ColdFusion language. In my code, I want to insert datetime into my database Oracle. Datetime format will look like this:

<cfset form.date_print = #dateFormat(Now(),"yyyy-mm-dd") & " " & timeFormat(now(), "HH:mm:ss")#>

and function to insert my datetime into my database will look like this:

<cfset qAdd = APPLICATION.qbs.insertData(column:'date_print,usr_id_print,qsd_id,loc_id',formData:'#FORM#',table:"qpl_print_log")>

In my table, format for attribute date_print is DATE.

This is snapshot when I click button to insert datetime into my database Oracle:

UPDATE

This is function to insert data to the database

<cffunction name="insertData" access="public" returntype="struct" output="no">
  <cfargument name="column"  required="true" type="string" hint="data">
  <cfargument name="value"  required="false" type="string" hint="data">
  <cfargument name="formData"  required="false" type="struct" hint="data">
  <cfargument name="table"  required="true" type="string" hint="table">
  <cfargument name="dateType"  required="false" type="string">
  <cfargument name="sqlNvarcharType"  required="false" type="string">
  <cfargument name="returnID"  required="true" type="boolean" default="true">
  <cfargument name="db"  required="false" type="string" hint="data" default="#variables.db.db_datasource_ro#">

  <cfset var local           = {}>
  <cfset local.counter       = 0>
  <cfset local.return        = {}>
  <cfset local.return.status = "OK">
  <cfset local.return.id     = 0>
  <cfset local.return.cuid = APPLICATION.cuid.getCUID()>
  <cfif ListFind(column,"submit")>
    <cfset column = ListDeleteAt( column, ListFind(column,"submit")) >
  </cfif>

  <cftry>
    <cfquery name="local.insertData" datasource="#db#" result="myresult">
      INSERT INTO  #table#(#column#,cuid) VALUES(
        <cfif isDefined('Arguments.value') AND Arguments.value NEQ "">
          <cfloop from="1" to="#ListLen(Arguments.value,';')#" index="item">
            <cfif isDefined('Arguments.dateType') AND ListFind(dateType,item)><cfqueryparam cfsqltype="cf_sql_date" value="#convertDate(trim(listGetAt(value, item,';')))#">
            <cfelseif isDefined('Arguments.sqlNvarcharType') AND ListFind(sqlNvarcharType,item)><cfqueryparam cfsqltype="cf_sql_nvarchar" value="#trim(listGetAt(value, item,';'))#">
            <cfelse><cfqueryparam cfsqltype="cf_sql_char" value="#trim(listGetAt(value, item,';'))#"></cfif><cfif item LT ListLen(column)>,</cfif>
          </cfloop>
        <cfelse>
          <cfloop list="#column#" index="theField">
            <cfset local.counter = local.counter+1>
            <cfif isDefined('Arguments.dateType') AND ListFind(dateType,theField)><cfqueryparam cfsqltype="cf_sql_date" value="#convertDate(formData[theField])#">
            <cfelseif isDefined('Arguments.sqlNvarcharType') AND ListFind(sqlNvarcharType,theField)><cfqueryparam cfsqltype="cf_sql_nvarchar" value="#trim(formData[theField])#">
            <cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(formData[theField])#"></cfif><cfif local.counter LT ListLen(column)>,</cfif>
          </cfloop>
        </cfif>
      ,'#local.return.cuid#')
    </cfquery>

    <cfif Arguments.returnID EQ true>
        <cfquery name="qetAutoIncrementID" datasource="#db#">
          SELECT id
          FROM #table#
          WHERE cuid = '#local.return.cuid#'
        </cfquery>
        <cfset local.return.id = qetAutoIncrementID.id>
    </cfif>

    <cfcatch>
        <cfset local.return.status = "BAD">
        <cfset local.return.cfcatch = cfcatch>
        <cfset APPLICATION.db.notifyError(functionname:"Insert:#table#", args:ARGUMENTS, cfcatch:cfcatch)>
        </cfcatch>
    </cftry>

    <cfreturn local.return>
</cffunction>

In my table, attribute date_print show date format only.

But in ColdFusion, I send the format date and time like this 2016-05-16 14:12:35.

But If I want to see my time format, I need to click the pencil icon.

Hope anyone can help me to fix this problem.

Thank you

I do not have any experience of oracle but seems oracle store data in DD-MM-YY format. please have a look at documentation docs.oracle.com/cd/B28359_01/server.111/b28318/… — Keshav jha
Basically I do not think that ColdFusion can't read the date and time format. What I think is it getting an error while execute to the database. I am not really sure how to solve this problem. Anyone have any idea? — Fai Zal Dong
CF can read date and time but you mentioned that you have problem inserting data into the database so it may be. Second thing you have not posted the query that is inserting the data. — Keshav jha
@Keshavjha I already update my post. Can you see anything problem? — Fai Zal Dong
If all you want to do is insert the current date and time into your Oracle db, forget ColdFusion and use the Oracle object, sysdate. — Dan Bracuk

Tushar Bhaware Tushar Bhaware · Accepted Answer · 2016-05-16T10:04:38

You are passing cfsqltype as cf_sql_date. You need to pass CF_SQL_TIMESTAMP as your cfsqltype.

Your cfqueryparam should look like this:

<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#convertDate(trim(listGetAt(value, item,';')))#">

cf_sql_date truncates date-time to just a date value (actually evaluates as a numeric value) where as cf_sql_timestamp uses a full date time stamp.

ColdFusion: Insert Date Time in One Variable Into Database Oracle10

2 Answers