tracker issue : CF-3309220

select a category, or use search below
(searches all categories and all time range)

ColdFusion 10 fails to insert utc datetime in right format

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): / ext-user (Swaraj Kumar Mitra)

Created: 08/13/2012

Components: Database, CFQuery

Versions: 10.0

Failure Type: Unspecified

Found In Build/Fixed In Build: 282462 / 282877, 282898

Priority/Frequency: Trivial / Unknown

Locale/System: English / Mac 10 All,Win XP All

Vote Count: 1

Problem: ColdFusion 10 can't convert  "#'utc Datetime with ODBC ' & utcDate#'  in to the correct format  while inserting in to the database. 

1. Install ColdFusion 10 on machine with built-in server
2. Place the scribble.cfm page in side the ColdFusion 10 root folder
3. Run this page in the browser

Result: The utc time value gets changed after inserting in to the database. The created utcDate (2012-08-10 11:20:37) will be inserted as 2012-08-10 13:20:37. It seems that there is an implicit conversion.

Images of have been attached.

Expected: It shouldn't change, as it was working fine in ColdFusion 9.

Workaround: NA

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

Watson Bug ID:	3309220

External Customer Info:
External Company:  
External Customer Name:  
External Customer Email:


  1. August 14, 2012 00:00:00: 1_scribble.cfm
  2. August 15, 2012 00:00:00: 2_CF9.JPG
  3. August 15, 2012 00:00:00: 3_CF10.JPG


This has been fixed partially. The bug where datetime functions like dateformat, timeformat and createodbcdate (when used on a utc date) return a local date instead of utc date has been fixed. But the issue of getting the local date from db instead of utc date should not be fixed though it is a regression. This happens because the db stores only the timestamp and the timezone information is lost. It was working in earlier releases because of a bug in the implementation.
Comment by Himavanth R.
18558 | September 13, 2012 01:23:25 AM GMT
Looking at the impact this had had on previous applications, we have decided to fix the second part of this bug as well. That will be tracked as part of bug # CF-3347145
Comment by Himavanth R.
18559 | October 22, 2012 07:22:25 AM GMT
Hi Himavanth, (just copying this from: In CF10's Release Notes, I didn't find mention this: CF10 changed the meaning of utc2local and local2utc. IMO, this is an important issue that deserves public input (bugbase doesn't facilitate this, since it doesn't notify commenters of new comments). The CF9 and CF10 documentation say: 1) dateConvert() returns "UTC- or local-formatted time object" 2) local2utc "Converts local time to UTC time" In Pre-CF10 and CF10, #1 is true. The value displayed on screen is UTC in both pre-CF10 and CF10. In Pre-CF10, #2 is also true. In CF10, #2 is ONLY true based on INTERPRETATION of the meaning of "Converts local time to UTC time". If reader interprets that as "Converts value from local time to UTC time", then #2 is untrue in CF10. Example: <cfscript> time1 = time2 = now(); writeOutput(dateConvert("local2utc", time1) is dateAdd("s", getTimeZoneInfo().utcTotalOffset, time2)); </cfscript> When server's time zone is not UTC: 1) above code returns Yes in Pre-CF10 and No in CF10 2) cfqueryparam sends value as UTC in Pre-CF10 but as local in CF10 3) serializeJSON() outputs value as UTC in Pre-CF10 but as local in CF10 Any applications that were migrated from Pre-CF10 to CF10 are currently experiencing silent data corruption if: 1) the server's time zone is not UTC 2) the app uses dateConvert() on date/time values before storing/sending them or after reading/receiving them The proposed workaround (the JVM arg to restore CF9 behavior) doesn't seem to be of use to the users most affected by this: Shared hosting customers. Shared hosting servers are typically not in UTC time zone and shared hosting customers typically lack the required access for applying a JVM argument. Others have mentioned this before, but I don't see it documented yet. I just saw another bug (#CF-3364479) filed over a month ago regarding this, but no feedback from Adobe on that ticket yet. Can this please be both blogged about and documented since it silently breaks migrated apps? Thanks, -Aaron
Comment by External U.
18560 | December 15, 2012 06:19:19 PM GMT
Just ran into this problem... trying to insert UTC date/time into sql server using this code: rightNow = Now(); nowUTC = DateConvert("Local2UTC", rightNow); crDateODBC = CreateODBCDateTime (nowUTC); the variable nowUTC is correct, but crDateODBC show the local time.
Vote by External U.
18561 | December 27, 2012 12:56:27 AM GMT