Skip to main content
G
ghouser
Inspiring
April 7, 2009
Answered

CFIF and date problems

  • April 7, 2009
  • 1 reply
  • 712 views

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>

This topic has been closed for replies.
Correct answer

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

1 reply

A
Anonymous
Correct answer
April 7, 2009

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
ilssac
Inspiring
April 7, 2009

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

G
ghouserAuthor
Inspiring
April 7, 2009

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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices