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

Answer 1

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

Answer 2

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">