tracker issue : CF-3564398

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

Java 7 Bug with Date Data Type

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/

Reporter/Name(from Bugbase): Joel Watson / Joel Watson (existdissolve)

Created: 05/21/2013

Components: Database

Versions: 9.0.1

Failure Type: Data Corruption

Found In Build/Fixed In Build: 9.0.1 /

Priority/Frequency: Critical / All users will encounter

Locale/System: English / Windows 7

Vote Count: 1

Problem Description: When using Java 7 (1.7.0_15...latest Adobe-certified Java version), dates of datatype "date" (SQL Server) are mangled in CF...to be more specific, the dates are exactly two days prior to the actual date stored in the database (e.g., 5-22-2013 is 5-20-2013 in CF). This occurs whether data is retrieved via CFQuery or ORM.

This only seems to affect datatypes of "date", as "datetime" columns represent the correct database values (see attached screenshot)

Steps to Reproduce: Install Java 1.7.0_15. Query a SQL Server table that has a "date" column. Dump the results, and you will see that the dates are exactly 2 days prior to what they are in the database.

Actual Result: When dates of "date" datatype (SQL Server) are retrieved with CFQuery or ORM, the date is 2 days prior to the actual date in the database.

Expected Result: Dates should correctly reflect the date stored in the database.

Any Workarounds: Downgrade to Java 1.6.0_xx

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

Watson Bug ID:	3564398

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



* Windows 7

* ColdFusion 9.0.2.282541

* Update Level: hf902-00005.jar

* Java Version - 1.6.0_35 (working)

* Java Version - 1.7.0_15 (produces bug)

Attachments:

  1. May 21, 2013 00:00:00: 1_DateBugQueries.PNG
  2. November 07, 2013 00:00:00: 2_repro_SQL_Create_Insert_Script.txt
  3. November 07, 2013 00:00:00: 3_repro_SQLData.PNG
  4. November 07, 2013 00:00:00: 4_repro_QueryDump-1.6.0_update45.png
  5. November 07, 2013 00:00:00: 5_repro_QueryDump-1.7.0_update15.PNG
  6. November 07, 2013 00:00:00: 6_repro_ColdFusion_output_test.cfm

Comments:

In the attached screenshot, you can see that the top (Java 1.7.0_15) produces the bug, while the bottom (Java 1.6.0_35) behaves as expected.
Comment by External U.
15327 | May 21, 2013 09:06:39 AM GMT
This bugs affects every usage of the date datatype in my app. I had to downgrade my Java version to fix it. I was on Java 7 Update 15. I am now using Java 6 Update 35.
Vote by External U.
15341 | May 21, 2013 11:38:59 AM GMT
It must be related to 9.0.2 as I'm not encountering this issue with 9,0,1,274733 (update level hf901-00009.jar) using Java 1.7.0__15.
Comment by External U.
15328 | May 21, 2013 12:06:37 PM GMT
NOTE: I'm using Windows 2008 (64bit) and installed the JDK instead of the JRE.
Comment by External U.
15329 | May 21, 2013 12:08:43 PM GMT
I tested the same code with Java 7 Update 21 and there is no change from Java 7 Update 15; it shaves off two days from the date stored in the DB. I also tested the code with Java 6 Update 45 and the dates display correctly. I am using 9,0,2,282541.
Comment by External U.
15330 | June 17, 2013 10:44:30 AM GMT
To those ppl testing/exprience this, if you switch back and forth between Java versions, does the issue happen / not happen reproduceably? -- Adam
Comment by External U.
15331 | July 29, 2013 06:18:09 AM GMT
thankfully not seeing that on cf10 w/1.7.21.0-b17 (though i can't find a production db where we use Date datatype, had gin up a table). any chance its the db driver? have you given microsoft's JDBC or jTDS drivers a shot?
Comment by External U.
15332 | July 29, 2013 07:46:09 AM GMT
Please attach a repro case and mention the database server which is being used. I have been trying it but unable to reproduce this issue.
Comment by Nimit S.
15333 | October 29, 2013 06:36:11 AM GMT
I have tried it with JDK 1.7.0_25. Can you also try with this version and let me know if you are facing this issue.
Comment by Nimit S.
15334 | October 29, 2013 06:43:10 AM GMT
@Nimit-- SQL Server 2008 As far as the repro case, all you have to do is create a table in the database with a column of "date" data type. Add some rows, and then query it. That should reproduce the issue (see screenshot).
Comment by External U.
15335 | October 29, 2013 06:45:11 AM GMT
existdissolve can you post an ACTUAL repro case, rather than just describe it (or show us a picture of it!). The actual code (incl. the table-create), which *demonstrates* the problem. SO we can run it, and see what we get. Reading: http://cfmlblog.adamcameron.me/2013/09/short-self-contained-correct-compilable.html -- Adam
Comment by External U.
15336 | October 29, 2013 07:15:10 AM GMT
Ok, I've added several files to help with reproducing this issue. * First, run the script to create the table and insert the test data (10 records) * In the test data, the "date" part of both columns ("date" datatype, and "datetime" datatype) should be identical * Run the CF template under Java 1.6.0_35. Record results * Run the CF template under Java 1.7.0_15. Record results and note differences Expected Results: When querying both columns, the dates (year, month, day) should be the same (ignoring the "time" part of the 2nd column). Actual Results: * In 1.6.0_35/_45, the results come out as expected (see repro_QueryDump-1.6.0_update45.png). Dates accurately reflect what is stored in database. * In 1.7.0_15 (and _17, _21, _25 ), the results come out unexpectedly, as previously reported (see repro_QueryDump-1.7.0_update15.png). The "datetime" datatype column (DateTimeDataType) is returned correctly. However, the "date" datatype column (DateDataType) does not, and strangely (but consistently) returns the date precisely 2 days previous to what it actually is in the database. So a date which is 2013-11-01 in the database is returned as 2013-10-30. In both examples, I did both a dump and a loop, just to rule out something stupid in cfdump. My environment details again are: SQL Server: 2008 R2 ColdFusion: 9.0.2.282541 Update Level: hf902-00005.jar
Comment by External U.
15337 | November 07, 2013 10:16:46 AM GMT
And in case there is any confusion, this is not about "dates" in general. It is specifically and (so far as I've seen) exclusively related to the actual"date" datatype in SQL Server, which was introduced in SQL Server 2008: http://technet.microsoft.com/en-us/library/bb630352.aspx
Comment by External U.
15338 | November 07, 2013 10:34:29 AM GMT
Found the issue. Apparently, it was an incompatibility between the Microsoft JDBC Driver that I was using and JRE 1.7. The jar I was using was mislabled (had same name as the correct version), so I was under the impression that it was the correct one. However, after digging into the jar, I discovered that it was an old version that did not have the compatibility features for the new "date" column type. Once I downloaded and installed the correct version of the driver, everything works correctly, for both 1.6+ and 1.7+. This ticket should be closed as it's not a CF issue.
Comment by External U.
15339 | December 11, 2013 02:20:27 PM GMT
Here are some articles with more info on the issue if anyone else encounters it: Same Complaint: http://stackoverflow.com/questions/7724258/date-columns-in-sql-server-mssql-jdbc-3-0-running-under-java-1-7-0-retrieved-a Driver Download: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
Comment by External U.
15340 | December 11, 2013 02:22:40 PM GMT