Using <CFIF> to compare two set of time

[phamtum]

I am using <cfif> to compare two set of time, if there is a match then display
link. I am doing something wrong because it not displaying a link even thought
there is a match. I using Access database, any assistance is greatly
appreciated!

Here is my code:

<CFQUERY DATASOURCE="testDB" NAME="gettasks">
select id, event_name, from_date
from calendar
where from_date >= ###dateformat(daterequest,'mm/dd/yyyy')###
AND from_date <= ###dateformat(daterequest,'mm/dd/yyyy')###
</CFQUERY>

<CFSET startTime = CreateTime(0,0,0)>
<CFSET endTime = CreateTime(23,59,59)>

<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" STYLE="border-collapse:
collapse" WIDTH="100%" ID="AutoNumber1">
<CFLOOP FROM="#startTime#" TO="#endTime#" INDEX="i"
STEP="#CreateTimeSpan(0,1,0,0)#">
<CFOUTPUT>
<TR>
<TD WIDTH="6%" HEIGHT="20" onClick="javascript:
popup('addevent.cfm?addDate=#daterequest#&addTime= #TimeFormat(i, "h:mm tt")#')"
VALIGN="TOP" ALIGN="right" BGCOLOR="##A0AB84" CLASS="smallWtext"
STYLE="cursor:pointer;cursor:hand; border-left: 1px solid ##EAEAEA; border-top:
1px solid ##EAEAEA; border-right-style:double;
border-right-width:3">#TimeFormat(i, "h tt")#&nbsp;</TD>
<TD WIDTH="94%" HEIGHT="20" STYLE="border-right:1px solid ##EAEAEA;
border-top:1px solid ##EAEAEA; border-bottom-style: dotted;
border-bottom-width: 1" BGCOLOR="##FFFFEB" CLASS="text">
<CFLOOP QUERY="gettasks"><!---problem can't match time --->
<CFIF TimeFormat(from_date, "h tt") is "#TimeFormat(i, "h tt")#">&nbsp;
<A HREF="editevent.cfm?id=#id#&daterequest=#datereque st#">?
#event_name#</A><BR></CFIF>
</CFLOOP></TD>
</TR>
<TR>
<TD WIDTH="6%" HEIGHT="15"
onClick="window.location='daily.cfm?disp=add&addDa te=#daterequest#&addTime=#Time
Format(i, "h:mm tt")#'; return true;" ALIGN="right" BGCOLOR="##A0AB84"
STYLE="cursor:pointer;cursor:hand; border-left: 1px solid ##EAEAEA;
border-right-style:double; border-right-width:3;<cfif #TimeFormat(i, "h tt")#
is "11:00 pm"> border-bottom: 1px solid ##EAEAEA</cfif>">&nbsp;</TD>
<TD WIDTH="94%" HEIGHT="15" STYLE="border-right: 1px solid ##EAEAEA;<cfif
#TimeFormat(i, "h tt")# is "11:00 pm"> border-bottom: 1px solid
##EAEAEA</cfif>" BGCOLOR="##FFFFEB">&nbsp;</TD>
</TR>
</CFOUTPUT>
</CFLOOP>
</TABLE>

[paross1]

Just curious... how can <b>from_date</b> be both greater than or equal to
daterequest, <b>AND</b> less than or equal to daterequest ,at the same time?
This statement will only return rows when from_date = daterequest.

<CFQUERY DATASOURCE="testDB" NAME="gettasks">
select id, event_name, from_date
from calendar
where from_date >= ###dateformat(daterequest,'mm/dd/yyyy')###
AND from_date <= ###dateformat(daterequest,'mm/dd/yyyy')###
</CFQUERY>

Phil

[phamtum]

My originally query was like this:

<CFQUERY DATASOURCE="testDB" NAME="gettasks">
select id, event_name, from_date
from calendar
where from_date >= ###dateformat(daterequest,'mm/dd/yyyy')###
AND from_date <= ###dateformat(daterequest,'mm/dd/yyyy') 23:59:59:997###
</CFQUERY>

But this did not work, once I removed the time it worked fine. thanks for
asking...

[paross1]

Since from_date is a date/time object containing a time component, it looks
like you want to restrict your results to a single day (24 hour period). You
left off the time part in your original post, so that is why it won't work.
Since you are using Access, you could use the Access Format() function to
remove the time component from your from_date as such:

<CFQUERY DATASOURCE="testDB" NAME="gettasks">
SELECT id, event_name, from_date
FROM calendar
WHERE Format(from_date, 'mm/dd/yyyy') = #dateformat(daterequest,'mm/dd/yyyy')#
</CFQUERY>

Since now you are comparing only date string to date string, you don't need to
include a time range test.
Phil

[phamtum]

Thank you for your advice, unfortunately it did not work. I have simplified my
codes to eliminate any other, but no luck.

You ara absolutely right on your assessment, I am looking to pull all record
with in a 24 hour period.

Here is the new short version of the code with your suggestion:

<CFPARAM NAME="daterequest" DEFAULT="#dateformat(now(), 'mm/dd/yyyy')#">

<CFQUERY DATASOURCE="bradpha_ps" NAME="gettasks">
select id, event_name, from_date
from calendar
WHERE Format(from_date, 'mm/dd/yyyy') = #dateformat(daterequest,'mm/dd/yyyy')#
</CFQUERY>



<CFSET startTime = CreateTime(0,0,0)>
<CFSET endTime = CreateTime(23,59,59)>

<CFLOOP FROM="#startTime#" TO="#endTime#" INDEX="i"
STEP="#CreateTimeSpan(0,1,0,0)#">
<CFOUTPUT>
#TimeFormat(i, "h tt")#
<hr>
<br>
<CFLOOP QUERY="gettasks">
<CFIF #timeFormat(from_date, "h tt")# is "#TimeFormat(i, "h tt")#">&nbsp; ?
#event_name#<BR></CFIF>
</CFLOOP>
</CFOUTPUT>
</CFLOOP>


Any other advice is greatly appreciated.

Thanks from your help!!

[paross1]

Is your query returning the expected data? (Put <cfdump var=#gettasks#> right after your query to view the results)

Phil

[phamtum]

I get "query - Rows: 0".

[paross1]

Do you know, for a fact, that your database has rows where from_date matches the date that you are requesting?

Phil

[phamtum]

I am 100% sure that there is a date in my database that matches the requesting
date. here is a copy of the database, I hope you can make it out.

id event_name from-date
from_time
1 Brandon's 1st test 5/30/2006 11:00:00 AM 11:00:00 AM
2 Brandon's 2nd test 5/30/2006 9:00:00 AM 9:00:00 AM
3 Brandon's third test 5/30/2006 11:00:00 AM 2:00:00 PM
5 Pick up Collin 5/31/2006 4:00:00 PM 4:00:00 PM
7 Web Tool 5/31/2006 9:00:00 PM 9:00:00 AM
10 my home page! 5/31/2006 8:00:00 AM 8:00:00 AM
11 High Level Budget 5/29/2006 12:00:00 PM 12:00:00 PM
12 Call Michael 5/29/2006 10:00:00 AM 10:00:00 AM
13 Call CardServices 5/29/2006 10:00:00 AM 10:00:00 AM

[paross1]

OK, lets try a different direction. Keep from_date as a date/time object and
use the createodbcdate() and createodbcdatetime() functions to convert your
daterequest variable to date/time objects and use BETWEEN to compare the range.

<CFQUERY DATASOURCE="bradpha_ps" NAME="gettasks">
select id, event_name, from_date
from calendar
WHERE from_date BETWEEN #createodbcdate(daterequest)#
AND #createodbcdatetime(daterequest+"23:59:59")#
</CFQUERY>

Phil

[phamtum]

Phil,

It worked, :D THANK YOU for your help!!