CFSpreadsheet Date formatting issue

Question

I'm running ColdFusion 10 locally and running into two issues with a database that was recently converted from Oracle to MSSQL.

What I'm trying to do is output data from the database into a spreadsheet using cfspreadsheet. There are 3 columns in a table and these contain date / timestamps.

Issue #1: The code worked fine prior to the conversion but after the conversion all the date / time stamps appear in scientific notation format. I've tried modifying my CFQUERY with a cast convert, but all this does is show the date / timestamp fields as 'YYYY/MM/DD' instead of my intended format which is 'MM/DD/YYYY HH:MM AM/PM.' When I double click on the cell in excel, I see the correct formatting but by default it shows it as 'YYYY/MM/DD.' Any suggestions here?

Issue #2: If the column or specific cell that is supposed to use the date / time formatting I specified is empty or null, I receive a '' is an invalid date or time string error.

Here is what I am using for the CAST/ CONVERT in my cfquery:

            ,CAST(CONVERT(varchar(20), GYMSTARTDATE, 22) AS datetime) AS GymStartDateTime
        ,CAST(CONVERT(varchar(20), GYMENDDATE, 22) AS datetime) AS GymEndDateTime

1) I am not being sarcastic, but ... if it "worked fine", what are you trying to change? :) 2) Some of the spreadsheet features are .. quirky. It would help if you put together a small, *self-contained* repro case that demonstrates the issue, so we can test it independently. Use http://sqlfiddle.com for the sql portion. 3) If you are getting errors, please include the *full* error message. It is hard to tell from that sentence if you are getting a SQL error or a CF error. — Leigh, Sep 04 '13 at 20:08
By "worked fine prior to the conversion", I mean when it was Oracle the date fields on the spreadsheet file that ColdFusion output appeared correctly. In other words, after the Oracle to MSSQL conversion was completed, all of the date fields appears in scientific notation format. So, I added the "CAST(CONVERT....." to the CFQUERY and the only thing it did was change the formatting from scientific notation to YYYY/MM/DD format in the excel file. — bman2013, Sep 04 '13 at 20:42

score 1 · Answer 1 · edited Sep 04 '13 at 23:38

If you ever figure it out, please let me know. Here's a link to the sample code I've been made available back in 2011. It doesn't work with ColdFusion 9 or 10. I've also listed all of the official "built-in" (but not supported) date formats.

http://pastebin.com/aQnembR3

<cfset q = queryNew("Name,Date", "varchar,date")>
<cfloop index="x" from="1" to="10">
    <cfset queryAddRow(q)>
    <cfset querySetCell(q, "Name", "Name #x#")>
    <cfset querySetCell(q, "Date", now())>
</cfloop>

<cfset dfStyle = {dataformat = "m/d/yy h:mm"}>

<cfset o = spreadsheetNew('dateTest', true)>
<cfset spreadsheetAddRows(o, q)>
<cfset spreadsheetFormatColumn(o, dfStyle, 2)>

<cfset bin = spreadsheetReadBinary(o)>

<cfheader name="Content-Disposition" value="attachment; filename=dateTest_#dateFormat(now(), 'ddmmmyyyy')#.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#bin#" reset="true">

What version of CF10? It worked fine for me in a quick test w/CF10 (unpatched). — Leigh, Sep 04 '13 at 21:55

score 1 · Answer 2 · answered Sep 04 '13 at 21:55

It is hard to say without seeing a data sample and your actual code. However, I tried to reproduce the issue you described with CF10 and SQL Server 2005, but could not. All of the values displayed as strings, not scientific notation.

It sounds like there is something different in your data, or environment, that we are not aware of. Please put together a small example that reproduces the issue and we can debug it further.

Test 1:

<!--- raw dates --->
<cfquery name="qry" datasource="#dsn#">
    SELECT  StartDate, EndDate
    FROM    YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test.xls" 
      query="qry" 
      overwrite="true" />

Result 1: (Cell Type: General)

STARTDATE                   ENDDATE
2013-08-20 14:19:28.907     2013-09-03 14:19:28.907
2013-08-25 14:19:30.293     2013-09-03 02:19:30.293

Test 2:

<!--- convert / datetime --->
<cfquery name="qry" datasource="#dsn#">
SELECT CAST(CONVERT(varchar(20), StartDate, 22) AS datetime) AS GymStartDateTime
          , CAST(CONVERT(varchar(20), EndDate, 22) AS datetime) AS GymEndDateTime
FROM   YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test2.xls" 
      query="qry"  
      overwrite="true" />

Result 2: (Cell Type: General)

GYMSTARTDATETIME            GYMENDDATETIME
2013-08-20 14:19:28.0       2013-09-03 14:19:28.0
2013-08-25 14:19:30.0       2013-09-03 02:19:30.0

Test 3:

<!--- convert / string --->
<cfquery name="qry" datasource="#dsn#">
SELECT  CONVERT(varchar(20), StartDate, 22) AS GymStartDateTime
           , CONVERT(varchar(20), EndDate, 22) AS GymEndDateTime
FROM   YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test3.xls" 
      query="qry" 
      overwrite="true" />

Result 3: (Cell Type: General)

GYMSTARTDATETIME            GYMENDDATETIME
08/20/13  2:19:28 PM        09/03/13  2:19:28 PM
08/25/13  2:19:30 PM        09/03/13  2:19:30 AM

CFSpreadsheet Date formatting issue

2 Answers2