Help with searching between dates

[shawntbanks]

I am trying to search a datebase for items that have a begindate, and an enddate. I don't want them to display if the current date is not between those two dates. can anyone help me

<cfset Now_date = #dateformat (now(), &quot;yyyy-mm-dd&quot# >

<CFQUERY datasource=&quot;market&quot; name=&quot;getvehicles&quot;>select subcategoryid, subcategory as vehicletype
, count(*) as vehicles, begindate, endate
from adds
where subcategoryid between '1' and '11' and #now_date# between begindate
and enddate
group by subcategory, subcategoryid
order by subcategory</CFQUERY><CFSET totalvehicles
= ArraySum(ListToArray(
ValueList(getvehicles.vehicles)))>

 

[ecobb]

Try this:

<cfset Now_date = #dateformat (now(), &quot;yyyy-mm-dd&quot# >

<CFQUERY datasource=&quot;market&quot; name=&quot;getvehicles&quot;>
select subcategoryid, subcategory as vehicletype, count(*) as vehicles, begindate, endate
from adds
where subcategoryid between '1' and '11'
and (begindate < #now_date# AND enddate > #now_date#)
group by subcategory, subcategoryid
order by subcategory
</CFQUERY>

If you can give some more details, we may be able to help futher. What database are you using? Are you getting any errors?

Hope This Helps!

Ecobb
- I hate computers!

 

[shawntbanks]

I tried that code and I still get now results. I have five items in my database and there should be 4 returned based on todays date. I did get an error if i do not include the enddate and the begindate in the group by clause. when I include them there, I still get no results


<P><STRONG>Vehicles:</STRONG> <span class=&quot;CategoryHeaders&quot;>(<CFOUTPUT>#totalvehicles#</CFOUTPUT> listings)</span></P>

</TD>
</TR>
<TR>
<TD>
<SELECT name=&quot;Vehicles_ID&quot; size=&quot;1&quot; onChange=&quot;jumpPage(this.form.Vehicles_id)&quot;>
<OPTION>--- Choose One ---</OPTION><CFOUTPUT query=&quot;getvehicles&quot;>
<OPTION value=&quot;#trim(getvehicles.subcategoryid)#&quot;>#getvehicles.vehicletype#
(#getvehicles.vehicles#)</OPTION></CFOUTPUT>