tracker issue : CF-3364479

Duplicate ID:	CF-3347145

Problem Description:
If I use "DateConvert("local2Utc",now())" to convert local time to utc everything seems to be fine at first. I can save the value to screen and it will be correct. But when when I use the value in cfqueryparam (cfsqltype="cf_sql_timestamp"), it inserts the wrong value (seems to be the local time instead of the utc time).

Steps to Reproduce:

Create the following database table:
- id (int, autoincrement)
- now (datatime)
- nowToUTC (datetime)
- nowToUTCToString (datetime)

Run the following script to reproduce the issue:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

<!--- create variables --->
<cfset datetime_local = now()>
<cfset datetime_utc = dateConvert("local2Utc",datetime_local)>
<cfset datetime_utc_tostring = datetime_utc.toString()>

<!--- show variables --->
<cfoutput>
	now: #datetime_local#<br>
	nowToUTC: #datetime_utc#<br>
	nowToUTCToString: #datetime_utc_tostring#"<br>
</cfoutput>

<hr>

<!--- insert variables in database (goes wrong) --->
<cfquery datasource="ds">
	INSERT INTO test ( now, nowToUTC, nowToUTCToString )
	VALUES (
		<cfqueryparam value="#datetime_local#" cfsqltype="cf_sql_timestamp">,
		<cfqueryparam value="#datetime_utc#" cfsqltype="cf_sql_timestamp">,
		<cfqueryparam value="#datetime_utc_tostring#" cfsqltype="cf_sql_timestamp">
	)
</cfquery>

<!--- insert variables in database (all fine) --->
<cfquery datasource="ds">
	INSERT INTO test ( now, nowToUTC, nowToUTCToString )
	VALUES (
		#datetime_local#,
		#datetime_utc#,
		#datetime_utc_tostring#
	)
</cfquery>

<!--- show records --->
<cfquery name="result" datasource="ds">
	SELECT now, nowToUTC, nowToUTCToString 
	FROM test2
</cfquery>
<cfdump var="#result#" metainfo="false" label="result">

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Actual Result:

now: {ts '2012-11-13 13:41:19'}
nowToUTC: {ts '2012-11-13 12:41:19'}
nowToUTCToString: {ts '2012-11-13 12:41:19'}"

result - query
    NOW                          NOWTOUTC               NOWTOUTCTOSTRING
1  2012-11-13 13:41:19.0  2012-11-13 13:41:19.0  2012-11-13 12:41:19.0  <--- using cfqueryparam (nowtoutc is 1 hour off)
2  2012-11-13 13:41:19.0  2012-11-13 12:41:19.0  2012-11-13 12:41:19.0 

Expected Result:

now: {ts '2012-11-13 13:41:19'}
nowToUTC: {ts '2012-11-13 12:41:19'}
nowToUTCToString: {ts '2012-11-13 12:41:19'}"

result - query
    NOW                          NOWTOUTC               NOWTOUTCTOSTRING
1  2012-11-13 13:41:19.0  2012-11-13 12:41:19.0  2012-11-13 12:41:19.0
2  2012-11-13 13:41:19.0  2012-11-13 12:41:19.0  2012-11-13 12:41:19.0 

Any Workarounds:
- Don't use cfqueryparam when inserting data into the database using "DateConvert("local2Utc",now())".
- Or use DateConvert("local2Utc",now()).toString() instead of DateConvert("local2Utc",now()).

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3364479

External Customer Info:
External Company:  
External Customer Name: CF_Coder
External Customer Email:  
External Test Config: My Hardware and Environment details:

ColdFusion 10 Update 4 (Update Level: 04)

Tomcat Version 	7.0.23.0

Operating System Windows Vista

Intel Core 2 Duo E7300 / 4GB RAM

Attachments:

Comments: