tracker issue : CF-4108544

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

cfSpreadsheet is incorrectly extracting year from dates

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/Fixed

Reporter/Name(from Bugbase): Mark Shute / Mark Shute (Mark Shute)

Created: 01/19/2016

Components: Document Management, Office Integration

Versions: 2016,11.0,10.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: CF11_Final / CF2018U5

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2003 Server

Vote Count: 2

Problem Description: When I convert a spreadsheet into a query object using <cfspreadsheet action="read" ...> and then output the query values, all dates prior to Jan 1, 1930 show the year as 20xx. For example, if the spreadsheet contains "1/19/1917", the output from the query (using dateFormat medium) would be "Jan 19, 2017". In addition, any dates after Dec 31, 2029 show the year as 19xx. For example, if the spreadsheet contains "1/19/2045", the output from the query (using dateFormat medium) would be "Jan 19, 1945". This may be due to the cfspreadsheet tag converting all years to 2-digit values. If you cfdump the query object immediately after creating it with cfspreadsheet, you will see all of the dates with 2-digit years. Using the previous examples, "1/19/17" and "1/19/45" respectively.

Steps to Reproduce: Create a spreadsheet. In one column list a series of dates. Include some dates prior to Jan 1, 1930 and after Jan 1, 2030. Use cfSpreadsheet with action "read" to convert the spreadsheet to a query object. Use cfoutput to output the query data using dateFormat() and a mask that includes a 4-digit year

Actual Result: Any dates prior to 1930 will be changed to 20xx. Any dates after 2030 will be changed to 19xx

Expected Result: The actual 4-digit years entered into the spreadsheet

Any Workarounds: none known.

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

Watson Bug ID:	4108544

External Customer Info:
External Company:  
External Customer Name: Mark Shute
External Customer Email:  
External Test Config: My Hardware and Environment details: CF11 running on Windows 2003

Attachments:

  1. January 20, 2016 00:00:00: 1_CF_2-digit_year_issue.png

Comments:

It appears that this may be more the fault of Excel and the default regional settings. If the cell formatting is changed from a regional date format to a static date format that includes 4-digit years, the cfspreadsheet tag will process the date with all four digits.
Comment by External U.
4875 | January 19, 2016 02:13:31 PM GMT
To elaborate on Mark's comment, here are the factors that contribute to this issue: When you use <cfspreadsheet> to read directly into a Query object, as opposed to a Spreadsheet object, then CF constructs the query with all column types set to VARCHAR. It fills the query with strings (and not ints or dates), regardless of the data types of the cells of the spreadsheet being read. The strings that are put in the query are the formatted values of the cells (i.e. as they would appear to the user in Excel). E.g. if a date is formatted as "Y: yyyy M: mm D:dd" in Excel, then the string in the query also looks exactly like that. That's all good and for most date formats that works as expected. The problem arises with the special "regional" date formats in Excel. "Regional" date and time formats are those that have an * in front of the format when looking in Excel. Regardless of how they may appear in the list of formats, when setting one of these, the user is telling Excel: "format this cell according to my system locale". If the user changes the system locale, the appearance of these formats (and hence the cells it is applied to) change. Internally, Excel still stores the date as a numeric value as always. I don't know how Excel stores the fact that the cell has a "regional" date format. The problem then happens when ColdFusion is trying to read such cells. It sees the numeric date value correctly, but it is falling back to using the short US date format of "m/d/yy". It does so regardless of the system locale or the locale set for the CF page. The puzzle is then complete: query gets filled in with the string that is the formatted value according to the date format of m/d/yy. For a comprehensive fix, CF should detect from the spreadsheet that the cell has a regional format. It should then apply the active locale of the CF page to format the string that it puts in the query.
Comment by External U.
4876 | April 12, 2016 05:34:11 AM GMT
Additionally, when using <cfspreadsheet> to read into a Spreadsheet object (instead of a Query object), then all cell formatting information is read and retained correctly, any cell that has a regional date format. This can be verified by writing such a Spreadsheet object back out to a file. SpreadsheetGetCellValue always returns a formatted string, and on a cell that has a regional date format it also applies the short US date format of "m/d/yy". Hence such a cell may appear to have only 2-digits of year, but internally in a spreadsheet object it is still stored as a numeric date value. This can be verified by applying an alternative date format and using SpreadsheetGetCellValue again.
Comment by External U.
4877 | April 12, 2016 05:46:52 AM GMT
+1, sigh, please fix.
Vote by Aaron N.
30674 | May 01, 2019 07:53:01 AM GMT