Date Problem with Query on Query Results

[SteveTadge]

I have created query to get certain dates from an Access database. As a calendar is created, I query the results of the first query to see if there is a match. I have not been able to get the query of the query results to match any of the dates I know are in the first query results. See code below. I am running CF 7 on Windows XP.

First Query Output with 4 records:
2006-09-03 00:00:00
2006-09-05 00:00:00
2006-09-10 00:00:00
2006-09-18 00:00:00

Sample of CF7 query debug output:
status (Datasource=, Time=20ms, Records=0) in C:\Web_Sites\Fantastic_Rentals\NewSite\ThreeCalendars2.cfm @ 23:03:09.009
select CalType
from avail_dates
where CalDate = {ts '2006-09-03 00:00:00'}

I have changing the WHERE cause syntax to every scenario to get a match, but nothing works. Any suggestions would be greatly appreciated.

[azadisaryev]

the code you have works fine. even though there are several things wrong with it...

1) try not to use reserved words (Year and Month) as variable names (as you have #Year#, #Month#. Year and Month are CF functions, and depending how you have defined your variables and other code in your page, this may be causing your errors... You better raname your vars to something like datYear and datMonth...
2) get rid of ## around the dateformat functions - no need for them inside cfset. your cfset statements should look like this: <cfset dayview = dateformat(createdate(NextYear, NextMonth, thisday), "m/d/yyyy")>

also, if FirstDay and dayview are only used in your queries and not displayed anywhere else on the page, no need to format them with dateformat() at all...

i guess there is NO match, that's why nothing is returned by your second query...

[SteveTadge]

I corrected the couple items in the code that were identified as not appropriate, but it still does not match the 4 records listed below. Each of these dates are in the month of September and should be matched by the second query as it loops through the month, if there were not some syntax or configuration problem. The output data and the CF7 debug query results show an example, which I bolded a specific instance where there is a match.

-----------------------------------------------------------
First Query Output with 4 records:
2006-09-03 00:00:00
2006-09-05 00:00:00
2006-09-10 00:00:00
2006-09-18 00:00:00

Sample of CF7 query debug output:
status (Datasource=, Time=20ms, Records=0) in ..\ThreeCalendars2.cfm @ 23:03:09.009
select CalType
from avail_dates
where CalDate = {ts ' 2006-09-03 00:00:00'}
------------------------------------------------------------------------------------------------

Is there some setting in CF7 that I need to change? I do not ever recall seeing the {ts } brackets used with a date before now.

Thanks.

[Dan_Bracuk]

Q of Q is unpredictable when it comes to data types. If it did assign a date datatype to avail_dates.caldate, either

where caldate = createodbcdate(2006-09-03)
or
where caldate = <cfqueryparamcfsqltype="cf_sql_date" value="2006-09-03">

should work, just like a database query.

[Newsgroup_User]

Try using a <cfqueryparam>.

--
Adam

[azadisaryev]

i presume you must have tried outputting the result of your first query and your dayview variable, but if you haven't add the following code below your second query, just to make sure the second query should return something:

<cfoutput query="avail_dates">
<pre>#CalDate#</pre>
</cfoutput>
<br /><br />
<cfoutput>
#CreateODBCDateTime(dayview)#
</cfoutput>

then check the outputs and make sure there is a date in the first output that matches the date in the second exactly.

as suggested by other posts, try using <cfqueryparam> in your where clause in the second query. just make sure there is a space between cfqueryparam and cfsqltype words.

you may also want to check that CalType is correct and returns something...

{ts ...} stands for timestamp and is a default return of unformatted dates/times.

[Swift]

Steve,

I believe the problem is being caused by different date formats. I don't believe that CF actually compares the fields as dates, but rather as strings. As such, 09/16/2006 will not equal {ts '2006-09-16 00:00:00'}, which I believe is how you have it.

Try this instead:

<cfquery name="status" dbtype="query">
select CalType
from avail_dates
where #CreateODBCDateTime(CalDate)# = #CreateODBCDateTime(dayview)#
</cfquery>

...or maybe some variation of that. Haven't tried it myself, just thought it might work.

HTH
Swift

[SteveTadge]

I tried the <cfqueryparam ..> tag with no luck. I got the following query output from sql debug when using it. The code I used for the second query is shown below. I also, tried the "createodbcdate(2006-09-03)" along with many other combinations. The code for the first query has not changed. Any thoughts on why it shows a "?" has the value or this normal?

-------------------------------------
status (Datasource=, Time=10ms, Records=0) in C:\Web_Sites\Fantastic_Rentals\NewSite\ThreeCalendars2.cfm @ 21:53:24.024
select CalType
from avail_dates
where CalDate = ?

Query Parameter Value(s) -
Parameter #1(cf_sql_date) = {ts '2006-09-01 00:00:00'}
----------------------------------------------------------------------------

The possible solution provided by Swift, gave an "undefined variable error for CalDate". I tried several variations with no luck.

Here is the output from Azadi's code. It shows that there is a match.

2006-09-03 00:00:00
2006-09-05 00:00:00
2006-09-10 00:00:00
2006-09-18 00:00:00

{ts '2006-09-03 00:00:00'}

Is there a way to format the query results in the first SQL query?

I really appreciate the suggestions.

Thanks,
Steve

[azadisaryev]

grrr... this is rediculous! it must work! your dates are same (even though they are displayed a bit differently, it will not cause a problem - i have tested it just now), so your second query must return a match...

now, i am sure you have done this, but just in case: did you explicitly output the result set of query 2 to check that it definitely does not return any records, or did you just use its result set in some other expression?
also, just to rule out any error in CalType, try this:

change your query 2 to select CalDate instead of CalType, and then <cfoutput query="status">#CalDate#</cfoutput>. if that does return your matched date, then there is some problem with CalType... you can then try to select * in query 2 and then output both #CalType# : #CalDate#.

i have tested your exact code with an Access db i have, and it works just fine.

another possible are to look into is locale date settings... but i do not think that could be causing the problem...

[SteveTadge]

That did not work either. I created another test template to make it simpler to adjust the code, which I have all listed below. If I change the code to querry the database instead of the results from the first query, it works fine. Besides having to use the ## to indicate a date data type for Access.

Thanks for your help.
Steve

------------------------------------------

[azadisaryev]

ok, let's try this. change the last 3 blocks of code you posted to this (see attached code).
uless, of course, you have tried this already...

[Jennycatg]

I just had this exact problem earlier today.

I also got this suggestion, but this code didn't work for me:

<cfquery name="status" dbtype="query">
select CalType
from avail_dates
where CalDate = #CreateODBCDateTime(dayview)#
</cfquery>

Once I started using the <cfqueryparam> it worked for me. (I am hitting an Oracle database rather than Access though.)

The only difference in my code is, instead of:

<cfset dayview1 = dateformat(createdate(2006, 9, 5), "m/d/yyyy")>

I have:
<cfset dayview1 = dateformat(createdate(2006, 9, 5), " mm/dd/yyyy")>

Hope this helps!

[SteveTadge]

I am sure there is some minor problem that I can not find in the code, but I can not afford to spend any more time trying to get this to work. Instead of trying to query the results from the first query, I have created a List from the first query results and are grabbing the data from the list. It works with the same principle.

Thanks for the help.

[Newsgroup_User]

Hi Steve
I created an Access DB, loaded your sample data@

2006-09-03 00:00:00
2006-09-05 00:00:00
2006-09-10 00:00:00
2006-09-18 00:00:00

And ran this query on it:

<cfquery name="qoq" dbtype="query">
select CalType
from avail_dates
where CalDate = <cfqueryparam value="#createDate(2006, 9, 3)#">
</cfquery>

And it returned the first record, as I would expect.

Is this what you're doing?

--
Adam

[Newsgroup_User]

> <cfset dayview1 = dateformat(createdate(2006, 9, 5), " mm/dd/yyyy")>

You should get rid of the dateFormat(): it's unnecessary and inappropriate.
dateFormat() is for formatting dates for "human consumption"; it just
confuses matters when you use it when talking to a computer. The DB is
expecting a date, so createDate() / createDateTime() (or in some
circumstances the ODBC versions of those functions) is all you need to use.

--
Adam