CSV file to Excel 2010 formats datetime fields to time

Question

I have several datetime fields in SQL Server that get outputted into CSV files, either as .txt or .csv. When automatically opening the .csv file in Excel 2010, it formats the datetime as mm:ss.0 and drops the date portion completely.

I have read this: Opening a CSV file in excel changes my formats and this: Date format that is guaranteed to be recognized by Excel

Here is a snippet of the raw text stored in the CSV file (.txt). Note the date portion is ISO format. The time portion does have milliseconds.

"EVENTSTARTDATE","EVENTTITLE","PURCHASEDATE"
"2013-04-17 00:00:00.0","Test Event","2013-04-17 15:06:27.56"

Here is the formatting in Excel:

EVENTSTARTDATE  EVENTTITLE  PURCHASEDATE
00:00.0         Test Event  06:27.6

Clicking on the fields show these values:

EVENTSTARTDATE                      PURCHASEDATE
4/17/2013  12:00:00 AM              4/17/2013  3:06:28 PM

When I go to "Format Cells..." and look at the field format, it is "Custom" and "mm:ss.0". I can reformat it to a Date format and it works fine, so obviously the data itself is correct. Why is Excel formatting only the time portion, and why dropping the hours? If the field type is "General", shouldn't Excel be able to parse the datetime data?

Other possibly relevant info: I am using ColdFusion 9 and have code that uses the CreateODBCDateTime() function.

<cfif isDate(raw)>
   <cfset raw = CreateODBCDateTime(raw)>
</cfif>

I'm going to look into the cfspreadsheet tag introduced in CF9: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html — Revent, May 15 '13 at 00:15
FYI: In future questions, you may want to include the main `coldfusion` tag as well to attract a wider viewing audience :) — Leigh, May 16 '13 at 03:09

score 1 · Accepted Answer · answered May 15 '13 at 00:57

Solved the issue. There was a function call buried in the code to this function:

<cffunction name="QueryToCSV" access="public" returntype="string" output="false" hint="Converts a query to a comma separated value string.">
        <cfargument name="Query" type="query" required="true" hint="the query being converted to CSV">
        <cfargument name="Headers" type="string" required="false" default="#arguments.query.columnList#" hint="the list of field headings to be used when creating the CSV value">
        <cfargument name="Fields" type="string" required="false" default="#arguments.query.columnList#" hint="the list of query fields to be used when creating the CSV value">
        <cfargument name="lstDateTimeFields" type="string" required="false" default="" hint="the list of fields that should be output in a date/time format">
        <cfargument name="CreateHeaderRow" type="boolean" required="false" default="true" hint="flags whether or not to create a row of header values">
        <cfargument name="Delimiter" type="string" required="false" default="," hint="the field delimiter in the CSV value">

        <!--- 
            Author:
            Ben Nadel 

            Link:
            http://www.bennadel.com/blog/1239-Updated-Converting-A-ColdFusion-Query-To-CSV-Using-QueryToCSV-.htm
        --->
...
</cffunction>

This is what it was using to convert the query to CSV. Setting the lstDateTimeFields argument with the correct field names caused them to format correctly in the Excel file.

CSV file to Excel 2010 formats datetime fields to time

1 Answers1