Hey Mark....I had this very same problem a few weeks back when doing date comparisons in QoQ. The answer: use <cfqueryparam cfsqltype="CF_SQL_DATE" value="#yourdate#"> and it should work fine. Basically forcing the date into a specified type is the only way. He is a basic version of the query which I got working use cfqueryparam:
<cfquery name="qSalesDetails" dbtype="query"> SELECT someDate FROM tempQuery WHERE someDate >= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateFrom#"> AND someDate < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateTo#"> </cfquery> Regards, Phil "Mark Woods" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > I'm trying to query a query which has a timestamp column. The query is > created using query functions, it is not the result of a db query, so the > issue I'm having is probably to do with the underlying type of the column... > > If i use ts < #createODBCDateTime(dateAdd("n","-#url.contentAge#",now()))# > in the WHERE clause I get the following error: > Query Of Queries syntax error. > Encountered "ts < {" at line 9, column 29. Incorrect conditional > expression, Expected one of [like|null|between|in|comparison] condition > > If I change the value stored in the query timestamp column to be an > iso/odbc/jdbc timestamp string YYYY-MM-DD HH:MM:SS value rather than the > result of now() function and try syntax "WHERE ts < '2003-04-03 12:30:00'" > I get an unsupported type comparison. > > I see that CAST can be used in CFMX, but as far as I know, it's not > available in CF5. I also know there are ways to get more info about the > query using the getMetadata() method of the query object in Java and I > presume there are ways to set the underlying types, but this wouldn't solve > my problem in CF5. > > Anyone know where I might find some more info about how to force the > underlying type to be a timestamp for both CF5 and CFMX which should > hopefully allow me to use < and > to compare dates. > > Excuse me if I'm not making much sense, I'm an hour late leaving the house > to meet my girlfriend 'cos I got tied up with this crap and I'm not > thinking straight anymore. > > Would really appreciate any insight into all this > > > Mark > > > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
