title: ColdFusion: Insert Date Time in One Variable Into Database Oracle10
body: <p>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:</p>
  
  <pre><code>&lt;cfset form.date_print = #dateFormat(Now(),"yyyy-mm-dd") &amp; " " &amp; timeFormat(now(), "HH:mm:ss")#&gt;
  </code></pre>
  
  <p>and function to insert my datetime into my database will look like this:</p>
  
  <pre><code>&lt;cfset qAdd = APPLICATION.qbs.insertData(column:'date_print,usr_id_print,qsd_id,loc_id',formData:'#FORM#',table:"qpl_print_log")&gt;
  </code></pre>
  
  <p>In my table, format for attribute date_print is <code>DATE</code>.</p>
  
  <p>This is snapshot when I click button to insert datetime into my database Oracle:</p>
  
  <p><a href="https://i.stack.imgur.com/jeHRH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jeHRH.png" alt="enter image description here"></a></p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This is function to insert data to the database</p>
  
  <pre><code>&lt;cffunction name="insertData" access="public" returntype="struct" output="no"&gt;
    &lt;cfargument name="column"  required="true" type="string" hint="data"&gt;
    &lt;cfargument name="value"  required="false" type="string" hint="data"&gt;
    &lt;cfargument name="formData"  required="false" type="struct" hint="data"&gt;
    &lt;cfargument name="table"  required="true" type="string" hint="table"&gt;
    &lt;cfargument name="dateType"  required="false" type="string"&gt;
    &lt;cfargument name="sqlNvarcharType"  required="false" type="string"&gt;
    &lt;cfargument name="returnID"  required="true" type="boolean" default="true"&gt;
    &lt;cfargument name="db"  required="false" type="string" hint="data" default="#variables.db.db_datasource_ro#"&gt;
  
    &lt;cfset var local           = {}&gt;
    &lt;cfset local.counter       = 0&gt;
    &lt;cfset local.return        = {}&gt;
    &lt;cfset local.return.status = "OK"&gt;
    &lt;cfset local.return.id     = 0&gt;
    &lt;cfset local.return.cuid = APPLICATION.cuid.getCUID()&gt;
    &lt;cfif ListFind(column,"submit")&gt;
      &lt;cfset column = ListDeleteAt( column, ListFind(column,"submit")) &gt;
    &lt;/cfif&gt;
  
    &lt;cftry&gt;
      &lt;cfquery name="local.insertData" datasource="#db#" result="myresult"&gt;
        INSERT INTO  #table#(#column#,cuid) VALUES(
          &lt;cfif isDefined('Arguments.value') AND Arguments.value NEQ ""&gt;
            &lt;cfloop from="1" to="#ListLen(Arguments.value,';')#" index="item"&gt;
              &lt;cfif isDefined('Arguments.dateType') AND ListFind(dateType,item)&gt;&lt;cfqueryparam cfsqltype="cf_sql_date" value="#convertDate(trim(listGetAt(value, item,';')))#"&gt;
              &lt;cfelseif isDefined('Arguments.sqlNvarcharType') AND ListFind(sqlNvarcharType,item)&gt;&lt;cfqueryparam cfsqltype="cf_sql_nvarchar" value="#trim(listGetAt(value, item,';'))#"&gt;
              &lt;cfelse&gt;&lt;cfqueryparam cfsqltype="cf_sql_char" value="#trim(listGetAt(value, item,';'))#"&gt;&lt;/cfif&gt;&lt;cfif item LT ListLen(column)&gt;,&lt;/cfif&gt;
            &lt;/cfloop&gt;
          &lt;cfelse&gt;
            &lt;cfloop list="#column#" index="theField"&gt;
              &lt;cfset local.counter = local.counter+1&gt;
              &lt;cfif isDefined('Arguments.dateType') AND ListFind(dateType,theField)&gt;&lt;cfqueryparam cfsqltype="cf_sql_date" value="#convertDate(formData[theField])#"&gt;
              &lt;cfelseif isDefined('Arguments.sqlNvarcharType') AND ListFind(sqlNvarcharType,theField)&gt;&lt;cfqueryparam cfsqltype="cf_sql_nvarchar" value="#trim(formData[theField])#"&gt;
              &lt;cfelse&gt;&lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(formData[theField])#"&gt;&lt;/cfif&gt;&lt;cfif local.counter LT ListLen(column)&gt;,&lt;/cfif&gt;
            &lt;/cfloop&gt;
          &lt;/cfif&gt;
        ,'#local.return.cuid#')
      &lt;/cfquery&gt;
  
      &lt;cfif Arguments.returnID EQ true&gt;
          &lt;cfquery name="qetAutoIncrementID" datasource="#db#"&gt;
            SELECT id
            FROM #table#
            WHERE cuid = '#local.return.cuid#'
          &lt;/cfquery&gt;
          &lt;cfset local.return.id = qetAutoIncrementID.id&gt;
      &lt;/cfif&gt;
  
      &lt;cfcatch&gt;
          &lt;cfset local.return.status = "BAD"&gt;
          &lt;cfset local.return.cfcatch = cfcatch&gt;
          &lt;cfset APPLICATION.db.notifyError(functionname:"Insert:#table#", args:ARGUMENTS, cfcatch:cfcatch)&gt;
          &lt;/cfcatch&gt;
      &lt;/cftry&gt;
  
      &lt;cfreturn local.return&gt;
  &lt;/cffunction&gt;
  </code></pre>
  
  <p>In my table, attribute date_print show <code>date format</code> only. </p>
  
  <p><a href="https://i.stack.imgur.com/fZ7vO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fZ7vO.png" alt="enter image description here"></a></p>
  
  <p>But in ColdFusion, I send the format date and time like this <code>2016-05-16 14:12:35</code>.</p>
  
  <p>But If I want to see my time format, I need to click the pencil icon.</p>
  
  <p><a href="https://i.stack.imgur.com/mFLPM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mFLPM.png" alt="enter image description here"></a></p>
  
  <p>Hope anyone can help me to fix this problem.</p>
  
  <p>Thank you</p>