tracker issue : CF-4204354

Problem Description:
The attribute cfsqltype="cf_sql_timestamp" of cfqueryparam and cfprocparam enforces a decimal point in the number of seconds, irrespective of the date format required by the user. This is crucial, particularly for Oracle databases, where cfsqltype="cf_sql_timestamp" is the only way for ColdFusion to pass datetimes safely to the database.

Apparently, this is caused by ColdFusion's conversion of the input into a java.sql.Timestamp object, including the nanos part. 

Steps to Reproduce:
1) Create a test database table having a column of date type (yyyy-mm-dd HH:mm:ss);

2) Run the following test code:
<!--- The format the user wants, as required by his or her database table--->
<cfset formattedDatetime=datetimeFormat(now(), "yyyy-mm-dd HH:nn:ss")>
<cfquery datasource="myDSN" result="res">
	INSERT INTO test_tbl values (<cfqueryparam cfsqltype="cf_sql_timestamp" value="#formattedDatetime#"  maxlength="19">)
</cfquery>
<cfdump var="#res#" >

Actual Result:
ColdFusion passes to the database a datetime that includes a decimal point. For example,  2019-05-17 20:53:36.0. This happens despite the specified date format and despite the maxlength.

Expected Result:
If the specified date format required it, the datetime should be passed without the decimal (nanos).

Any Workarounds:
In place of a timestamp object, use a concatenation of a date and a time. This of course depends on the database brand.

For example, in MySQL:
concat(<cfqueryparam cfsqltype="cf_sql_date" value="#formattedDatetime#">,' ',<cfqueryparam value="#formattedDatetime#" cfsqltype="CF_SQL_TIME">)

In Oracle:
<cfqueryparam cfsqltype="cf_sql_date" value="#formattedDatetime#"> || ' ' || <cfqueryparam value="#formattedDatetime#" cfsqltype="CF_SQL_TIME">

Attachments:

Comments: