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.

Good thought, but he is using a special in memory query, which does not support the `convert` function. — Leigh, Mar 20 '12 at 17:44

score 3 · Accepted Answer · answered Mar 21 '12 at 04:39

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

(A) did not work for me as it threw an error "{ts '2011-01-07 00:00:00'} is an invalid date or time string". (B) However works pretty good. In case of large data it may be a little slow since it re-writes the spreadsheet but that's not a problem for me. So thanks for your suggestion and thanks to Sean who initially proposed the "SpreadsheetFormatColumn" idea. — user1201723, Mar 21 '12 at 09:22
If you are using A) you no longer need to `CAST` the result because it is already a date object. Sounds like that might be the cause of your error. Get rid of any date casting in the QoQ and it should work fine. — Leigh, Mar 21 '12 at 15:58
Indeed CAST was the culprit, I removed it and solution (A) works great now. — user1201723, Mar 21 '12 at 16:46
Good. Btw, if you found Sean's answer helpful too feel free to upvote it. — Leigh, Mar 21 '12 at 17:10

score 2 · Answer 2 · answered Mar 20 '12 at 16:31

2

You should be able to use SpreadsheetFormatColumn on the date columns with the dataformat setting to set the proper date format for that column.

You can find more information on SpreadsheetFormatColumn and the dataformat setting in the ColdFusion 9 documentation:

SpreadsheetFormatColumn: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6806.html

Formatting options: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6747.html

answered Mar 20 '12 at 16:31

Sean Coyne

3,864
21
24

Sean: The date is already formatted correctly in the spreadsheet. The problem is not its format but the datatype returned by cfspreadsheet which treats all values returned as "VarChar" and thus prevents a proper sorting. – user1201723 Mar 20 '12 at 16:42
2

@user1201723 - I believe `CAST` assumes US date format. So probably the best you can do is either a) change the underlying spreadsheet format so it returns a US date string, as Sean suggested OR b) loop through the query first and parse the EURO date strings manually. – Leigh Mar 20 '12 at 16:54
@user1201723 apologies, I read the question backwards. Thought you were going from a query to a spreadsheet. I should slow down and read. Have you tried cast as timestamp instead of date? Perhaps that will give you a proper data type you can use? You can find the documentation for the cast function here: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec22c24-7b7b – Sean Coyne Mar 20 '12 at 20:10
@Sean - Actually the idea has merit because the cell format is what CF/POI uses to create the returned date string. Unfortunately, I suspect any attempt to parse a Euro date string `dd/mm/yyyy` using standard functions will fail/ because non-LS date functions always assume U.S. date format. So for ambiguous cases like `01/07/2012` the sorting/actual value would still be incorrect. – Leigh Mar 20 '12 at 20:22
I would also second @Leigh's suggestion. You could use the US date format to handle your sort and then when you display them use dateformat or lsdateformat to properly display them in your desired Euro format. Whether they are in US or Euro format shouldn't have any bearing on how the sort is handled. – Sean Coyne Mar 20 '12 at 20:22
@Leigh that is assuming that its in the query as a string (varchar). if it is successfully cast to a date or timestamp then its not a string and `01/07/2012` vs `07/01/2012` shouldn't make a difference because the underlying variable is a "date" not a string so the order of the date/month shouldn't matter for sorting purposes. – Sean Coyne Mar 20 '12 at 20:29
Then again, if cast as date incorrectly sees 01/07 as Jan 7th instead of 1 July then, yes that will be an issue and formatting the spreadsheet column before its read so that the POI library recognizes it as a Euro date vs a US date might be the best solution. – Sean Coyne Mar 20 '12 at 20:31
1

@Sean - They are definitely returned as strings (not dates). I am pretty sure CAST assumes U.S. format, like all the standard date functions. That is why I think those are the two simplest options (ie 1) looping and parsing manually or 2) changing the cell format). – Leigh Mar 20 '12 at 20:38

score 1 · Answer 3 · answered Mar 21 '12 at 00:06

1

I think what you want to do is use the setLocale() function to set the locale to a European region, then do your query-of-query with the CAST() function:

<cfset setLocale("French (Standard)") />

<cfquery name="mySpreadsheet2" dbtype="query">
   select (CAST(date_field as DATE))   as mydate
     from mySpreadsheet order by mydate
</cfquery>

I just tried it with VARCHARs selected from a database (using Oracle TO_CHAR(date_field, 'DD/MM/YYYY')) and it worked. Don't know if you'll have the same result with <cfspreadsheet> though.

Hope this helps.

answered Mar 21 '12 at 00:06

David Faber

12,277
2
29
40

No, SetLocale does not affect `CAST`. It still interprets the strings as `mm/dd/yyyy`. So while it might appear to work, the date strings are actually interpreted incorrectly ie `01/07/2012` becomes `January 7`, not `July 1`. – Leigh Mar 21 '12 at 02:31
@Leigh, right you are - thanks for the correction. I was fooled because I was using dates like `26/07/2010` which CF interpreted as `July 26`. – David Faber Mar 21 '12 at 13:16

Query of queries and CAST as date

3 Answers3