select * where date1 >= start and date2 >= end 1

[josel]

I'm working on a template intended to display current events. Each event can be scheduled with an effective and expiration date.

The QUERY:

<cfquery name=&quot;shwList&quot; datasource=&quot;deco&quot; dbtype=&quot;odbc&quot;>
select *
from mthSaleEvent
where (#dateformat(mthEffective,&quot;mm/dd/yyyy&quot# >= #dateformat(now(),&quot;mm/dd/yyyy&quot#)
AND (#dateformat(mthExpires, &quot;mm/dd/yyyy&quot# <= #dateformat(now(),&quot;mm/dd/yyyy&quot#)

order by mthExpires
</cfquery>

Fields in DB are set to date/time format. I read some posts in this forum and found that to guarantee consistancy, I would need to use dateformat() ...

Please help me figure out what's wrong with above query!

Thank you all in advance;

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[webron]

If the field in the db is a date, you might try:

<cfquery name=&quot;shwList&quot; datasource=&quot;deco&quot; dbtype=&quot;odbc&quot;>
select *
from mthSaleEvent
where mthEffective >= #createOdbcDate(now())#
AND mthExpires <= #createOdbcDate(now())#
order by mthExpires
</cfquery>

webron

 

[josel]

That works,

Thank you!!!!

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours