Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

datecompare problem

Status
Not open for further replies.
junkjones

junkjones

Programmer
Jul 14, 2000
52
0
0
GB
Hi Everyone,

Here's my problem this week ;)

I have a database full of events. The eventday field holds the date of the event - in access it is a date/time field. On the events page of my site, I list all the events. What I want to do is list only the ones that are today, or in the future. So I use DateCompare like this:

<cfset today=dateformat(Now(),&quot;mm-dd-yy&quot;)>

<CFIF DateCompare(eventday, today) NEQ -1>
blah blah blah
</CFIF>

This works to return all the future events, but it won't show any of the current days' events. I'm ASSUMING this is because the database specifies the time along with the date, so it thinks all the events are happening at midnight, since all the times are 00:00:00. So if an event is scheduled to happen today, it thinks it already happened at midnight that morning. So how do I get datecompare to ignore the time? I've already tried using dateformat to format everything with just dates, no times, but that doesn't seem to work. Any ideas?
 
Sort by date Sort by votes
Make sure you're using dateFormat both on #today# and #eventdate# . As long as both values of the datecompare() function are in the same format, everything should be fine.

I built a list of dates in the format most databases will store the date/ time in. Im looping through this instead of a query, but this is the same approach you should try.

<html>
<CFSET todayDate = dateFormat(now(), &quot;m-d-yyyy&quot;)>
<CFSET eventDateList = &quot;2001-03-16 04:33:34,2001-03-19 04:33:34,2001-04-20 04:33:34,2001-01-01 04:33:34&quot;>
<CFOUTPUT>
<CFLOOP from=&quot;1&quot; to=&quot;#listLen(eventDateList)#&quot; index=&quot;incr&quot;>
<CFSET eventDate = listGetAt(eventDateList,incr)>
<CFSET eventDate = dateFormat(eventDate, &quot;m-d-yyyy&quot;)>

<CFIF DateCompare(eventdate, todaydate) NEQ -1>
Event is >= today. <br>
</CFIF>

Event Date: #eventDate#<br>
Today's Date: #todayDate#<Br><hr>


</CFLOOP>



</CFOUTPUT>

</html>

Let me know if I helped.
 
Upvote 0 Downvote
Hey Junk,

I would restrict the dates on the database side and not worry about screening them out in the cfquery. I think this will do what you want.

<cfquery....

select * from tableName
where eventDay >= #createodbcdatetime(&quot;#dateformat(now(),&quot;mm/dd/yyyy&quot;)# 00:00:00&quot;)#

</cfquery>

Hope this helps,
GJ
 
Upvote 0 Downvote
I tried both and they both work great :)
Thanks guys once again for your help!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
106
mdvp
mdvp
ibeenjammin
  • Locked
  • Question
FrameMaker 2015 Conditional Text Problems
Replies
0
Views
108
ibeenjammin
ibeenjammin
jdhilljr
  • Locked
  • Question
Problem with cfquery
Replies
7
Views
63
cfSearching
cfSearching
lwfg
  • Locked
  • Question
session variable timeout problem
Replies
2
Views
49
lwfg
lwfg
pixiesfb
  • Locked
  • Question
Office 2007 cfheader cfcontent Word docx problem
Replies
5
Views
80
cfSearching
cfSearching

Part and Inventory Search

Sponsor

Back
Top