CFIF and date problems

[ghouser]

First, I know this should go under General Discusion, but the new system won't let me post there. I think there are bugs with the new forum pages.

Anyway.... Here's the problem.

I have a table storing starting dates and ending dates for promotional flyers. The table also lists the flyer PDF filename so a lick can be made for it in the output. My problem is that no matter how I put the logic in the CFIF I get either all or none of the flyers to list on the ouput. My desire is to have a flyer only show during its promotional dates. Here's what I've got so far. This seems like it should be easy, but I've been staring at it too long. Thanks for any suggestions  you may have.

<cfquery name="GetFlyers" datasource="sales">
select * from flyers
order by startdate
</cfquery>

<table>

<cfif #dateformat(GetFlyers.endDate,"m/d/yy")# GTE #NOW()# and #dateformat(GetFlyers.startDate,"m/d/yy")# LTE #NOW()#>

<cfoutput>
<tr><td><h4>#dateformat(GetFlyers.startDate,"m/d/yy")# thru #dateformat(GetFlyers.endDate,"m/d/yy")#</h4></td></tr>
</cfoutput>

</cfif>

</table>

[SherlockTicklesquire · Accepted solution]

I'd suggest using DateCompare("date1", "date2" [, "datePart"]) to perform the evaluation, since it works specifically on Date/time values.  I typically use datePart = "d" to compare to the day.  The "GTE" operators in your code are probably performing string, not date/time, comparisions and may never yield desired results.

DateCompare returns:
• -1, if date1 is earlier than date2
• 0, if date1 is equal to date2
• 1, if date1 is later than date2

[ilssac]

Nothing probable about it, the original code is comparing a string that represents a date to humans to a number the represents a date to a computer.

To confirm the dateCompare() function is what is desirable here but I want to take a moment to expand on what the original code was doing.

if #dateformat(GetFlyers.endDate,"m/d/yy")# GTE #NOW()#  This comes down to the following process inside the computer.

IF the string '7/4/09' IS GREAT THEN OR EQUAL TO the timeStamp 14567896756  [NOTE: all values are completely imaginary]

One has to understand that dates are not simple things that many assume they are and work with them appropriately.

HTH

Ian

Message was edited by: Ian Skinner

[ghouser]

I hadn't run across DateCompare(), but now that Sherlock pointed me to it, I see I'll use it more and more.  Thanks also to Ian for his imput. I did note you  really have to think about the -1, 0, and 1 values however. Had to scratch my head a bit on that part. 😉  I am now producing a list of promotion dates (from/thru) which I'll turn into a link to the actual PDF. This is just what the doctor ordered. I also will use this same technique on another page to link from a manufacturer logo and perform an item search to show the actual promo items from that manufacturer. This is great guys. Thanks!

My final working code now looks like this:

<cfquery name="GetFlyers" datasource="sales">
select * from flyers
order by startdate
</cfquery>
    
<cfoutput query="GetFlyers">
    <cfset ValidBeforeToday = DateCompare(#startDate#, #Now()#,"d")>
    <cfset ValidAfterToday = DateCompare(#EndDate#, #Now()#,"d")>

                <cfif ValidBeforeToday GTE -1 and ValidAfterToday GTE 0>
                  #dateformat(GetFlyers.startDate,"m/d/yy")# thru #dateformat(GetFlyers.endDate,"m/dd/yy")# <br />
                </cfif>
</cfoutput>

[ilssac]

I did note you  really have to think about the -1, 0, and 1 values however. Had to scratch my head a bit on that part. 😉

Yes, even after years of CF development dealing with all types of date issues, I still have to paper proof the logic of a dateCompare if statement.