How to handle date in cfquery ?

Question

Hello,

I have a query (qVIEW) which selects a date (which is one column in my table).

Now I want to loop thru the first query (qVIEW) and then do another query in it (qRate).
Actually my problem is with the qRate "where" statement. I want to put :

where "....and tbl_daily.adate= qVIEW.lastdate"

I added it (see below) but it is giving me error.
Am I putting this line wrongly?

Any help would be highly appreciated.

thx

<cfset locale =SetLocale("English (UK)")>

<cfquery name="qVIEW" datasource="mydb">
Select
tbl_view.name as myname,
tbl_view.lastdate as lastdate
from tbl_view
</cfquery>

<cfloop query="qVIEW">

<cfquery name="qRate" datasource="mydb">
select
tbl_daily.rate as dailyrate
from tbl_daily,tbl_names
where
tbl_names.names = '#qPL.myname#'
and tbl_names.id= tbl_daily.id
and tbl_daily.adate= qVIEW.lastdate 
order by tbl_daily.id desc
</cfquery>

</cfloop>

ics3djc · Accepted Answer

if I am understanding this correctly... put # # around qVIEW.lastdate so it looks like this

and tbl_daily.adate= #qVIEW.lastdate#

and it should work. if lastdate is a variable from another query you need the output ## so your new query knows what it stands for......

otherwise your query will try and look in tbl_daily.adate for a value of "qVIEW.lastdate"

hope this helps.

P.S. if that solves the problem you will probably also need to change the line above (and tbl_names.id= tbl_daily.id) to include ## also: and tbl_names.id= #tbl_daily.id#

Dan_Bracuk · Answer

It would be a lot easier and more efficient if you just ranone query and joined on the two date fields.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded