Query of queries and CAST as date

Question

I am using cfspreadsheet to read the values out of an excel sheet and then I do a query of queries to sort the date field because all fields returned by cfspreadsheet are of the "VarChar" type. Here is the code:

<cfspreadsheet action = "read" query = "mySpreadsheet" src = "mp.xls" sheet="1" rows="2-178"> 

<cfquery name="mySpreadsheet2" dbtype="query">
 select  
(CAST(date_field as DATE))   as mydate

from mySpreadsheet order by mydate

</cfquery>

In the spreadsheet the dates are in Euro format dd/mm/yy. The problem is the CAST function in the query of queries turns the 'date_field' from varchar to date but the American type of date (month first, day after).

For example the excel date_field column contains this value 01/07/2011 (July first 2011 since it is a Eurodate) but it gets converted to {ts '2011-01-07 00:00:00'} in the query of queries.

Is there a way to use CAST in the query of queries to produce a European style date? To get around this in oracle you would do: to_date(date_field, 'DD:MM:YY') but I don't know how to tackle this here.

Answer 1

As mentioned in some of the comments, CAST applies U.S. date formatting rules when parsing date strings. So an ambiguous value like 01/07/2011 will always be interpreted as January 7th.

Since the date format is known, I think the two simplest options are to either:

A) Iterate through the query and parse the date strings manually:

<cfloop query="yourQuery">
    <cfif LSIsDate(yourQuery.DateCol, "English (UK)")>
        <cfset yourQuery.DateCol[currentRow] = LSParseDateTime(yourQuery.DateCol, "English (UK)") />
    </cfif>
</cfloop>

-==OR

B) Change the underlying cell format as Sean suggested. Apply a U.S. format like mm/dd/yyyy so the returned strings will be correctly parsed by CAST. Or you could simply apply a non-ambiguous format like yyyy-mm-dd, which would sort correctly even as a string.

<!--- read in the workbook --->
<cfset sheet = spreadSheetRead("c:/path/file.xls")>
<!--- apply the new format and save back to disk --->
<cfset SpreadSheetFormatColumn(sheet, {dataFormat="yyyy-mm-dd"}, yourDateColumn)>
<cfset SpreadSheetWrite(sheet, "c:/path/file.xls", true)>
<cfspreadsheet action="read" query="yourQuery" src="c:/path/file.xls" sheet="1" rows="2-178" >