ColdFusion spreadsheet error, invalid date or time string

Question

I am trying to export a query to an Excel document.

Here is the code I am using:

<cfscript> 
    //Use an absolute path for the files. ---> 
    theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    theFile=theDir & "invoicesDue.xls"; 
    //Create an empty ColdFusion spreadsheet object. ---> 
    theSheet = SpreadsheetNew("invoicesData"); 
    //Populate the object with a query. ---> 
    SpreadsheetAddRows(theSheet,invoicesDue);  
</cfscript>

<!--- Write the sheet to a file ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="invoicesDue" overwrite=true>

The error I am getting is:

'' is an invalid date or time string. 

121: SpreadsheetAddRows(theSheet,invoicesDue); 

The thing is, I have dumped out my query and there are no quotation marks anywhere to be seen and all the date/time cells are either populated with a datetime such as "2011-03-31 00:00:00.0" or an empty string.

I wondered if anyone else has encountered this error before as I can't see the cause for it.

Answer 1

cfspreadsheet doesn't like empty values for date fields. In the end I used:

CAST(emptyDate as varchar) 

Which seems to be a fix.