-
1. Re: Query of Queries With Date Strings
Perhaps empty space at the end of the URL? If so, then trim(url.thedate) should do the trick. In any case, why not use varchar instead?
-
2. Re: Query of Queries With Date Strings
I tried using varchar. It didn't affect the results. I checked for whitespace around the "thedate" column in the query result but not in the url variable. However, if that was the issue, I would think that the query would have returned the record with the hard coded value, but it didn't.
Bear in mind that I am looking for opinions more than help. Replacing the equal sign with the like keyword solved my immediate problem. It just bugs me that something that I think should have worked, didn't.
-
3. Re: Query of Queries With Date Strings
Hardcode the 'thedate' results in PatientResources as well, ie, select
blah1,blah2,thedate='2010-02-11' , does your QOQ work then?
-
4. Re: Query of Queries With Date Strings
I see in the documentation that the argument DateRequested should be an Oracle date object. Is it? What about createODBCDate(DateRequested) instead? Could there be some conversion going on between cfqueryparam and URL, like - being converted into %2D and back?
-
5. Re: Query of Queries With Date Strings
Like this?
x (Datasource=cmpi, Time=0ms, Records=1) in D:\DW\dwtest\Dan\abc.cfm @ 08:16:41.041
select '2010-06-11' y from dual
z (Datasource=, Time=16ms, Records=0) in D:\DW\dwtest\Dan\abc.cfm @ 08:16:41.041select * from x where y = '2010-06-11'
and the same thing with mssql
x (Datasource=kidcare, Time=47ms, Records=1) in D:\DW\dwtest\Dan\abc.cfm @ 08:18:58.058
select '2010-06-11' y
z (Datasource=, Time=0ms, Records=0) in D:\DW\dwtest\Dan\abc.cfm @ 08:18:58.058select * from x where y = '2010-06-11'
-
6. Re: Query of Queries With Date Strings
DateRequested is an oracle date object and to_char returns a string. cfqueryparam does not appear to matter because I get the same results without it.
-
7. Re: Query of Queries With Date Strings
You might find that when you use CHAR columns / data, it will be space-padded by Oracle (so won't be showing up in your examination of whether there's extra whitespace) to be the specified length of the column (char data is fixed-length), which you won't really want. Use a VARCHAR.
Dunno why the LIKE is working, but.
--
Adam
-
8. Re: Query of Queries With Date Strings
Dan Bracuk wrote:
DateRequested is an oracle date object and to_char returns a string. cfqueryparam does not appear to matter because I get the same results without it.
Quite a satisfactory response.
-
9. Re: Query of Queries With Date Strings
Adam Cameron. wrote:
You might find that when you use CHAR columns / data, it will be space-padded by Oracle (so won't be showing up in your examination of whether there's extra whitespace) to be the specified length of the column (char data is fixed-length), which you won't really want.
Not currently on Oracle myself, but this sounds plausible.
-
10. Re: Query of Queries With Date Strings
And with a cold fusion query:
<cfscript>
q = QueryNew("s","varchar");
x = QueryAddRow(q, 1);
x = QuerySetCell(q, "s", "2010-06-10");
</cfscript>
<cfquery name="z" dbtype="query">
select s from q
where s = '2010-06-10'
</cfquery>
<cfdump var="#q#" metainfo="no" label="q">
<cfdump var="#z#" metainfo="no" label="z">q - query S 1 2010-06-10 z - query S -
11. Re: Query of Queries With Date Strings
This looks like a bug to me. I tried your example and got the same results in CF8 and CF9, though it works as expected in Railo...
It seems like it's trying to guess that you really wanted that to be a date. Change your example from
q = QueryNew("s","varchar");
to
q = QueryNew("s");
And you'll see:
q - query
S
1
{ts '2010-06-10 00:00:00'}
z - query
S
1
{ts '2010-06-10 00:00:00'}