6 Replies Latest reply on Mar 15, 2008 5:19 AM by -==cfSearching==-

    dateCompare and output problem

    Donaldino
      Hi everybody. Im making an events calender for a website from a cultural centre. I would the eventDate to be updated automatically when the eventDate is passed. To do so I used dateCompare.

      To test the functionality I inserted two events in the database. One for Saturday March 15 and one for Sunday March 16. But in the output both eventDates are Saturday March 15.

      Does anybody see what im doing wrong?

      thank you in advance
        • 1. Re: dateCompare and output problem
          Level 7
          well, where to begin...

          <cfquery name="rsEvents" datasource="Flyagain">
          SELECT eventID, eventDate, eventHeader, eventContent
          FROM events
          </cfquery>
          <cfset newDate = (rsEvents.eventDate)>
          <cfif dateCompare(DateFormat(Now()),rsEvents.eventDate) gt 0>
          <cfset newDate = DateAdd("d", 7, newDate)>
          <cfquery name="rsUpdate" datasource="Flyagain">
          UPDATE events
          SET eventDate = #newDate#
          Where eventDate = #newDate#
          </cfquery>
          </cfif>

          this line in your code :
          <cfset newDate = (rsEvents.eventDate)>
          will always evalueate to the value of eventDate of the FIRST ROW in
          rsEvents query because it is not inside a cfoutput query="rsEvents or a
          cfloop query="rsEvents"


          same for next line:
          <cfif dateCompare(DateFormat(Now()),rsEvents.eventDate) gt 0>
          again, the comparison is always made to the FIRST ROW value only

          in the next line you are setting your newDate var to a new value, but
          then in your next query you have Where eventDate = #newDate# - you do
          realise that this newDate is now 7 days later, right? you are updating
          DIFFERENT EVENTS as pulled by your first query. furthermore, your update
          sets these different events to same date as they are already set to.



          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com/
          • 2. Re: dateCompare and output problem
            Donaldino Level 1
            Thank you for your responce! How does the code needs to look like to make it work?
            • 3. Re: dateCompare and output problem
              -==cfSearching==- Level 4
              In addition to the problems Azadi has already mentioned

              Im making an events calender for a website from a cultural centre. I would the eventDate to be updated automatically when the eventDate is passed.

              It is not clear what you mean by that. Can you describe your goal in plain english (ie without code)?
              • 4. Re: dateCompare and output problem
                Donaldino Level 1
                Ok. I want the eventDate to be automatically updated. I mean when there is an event on Saturday March 16 (today) The date in the event calendar show Saturday March 16. Then on Sunday the date in the database should be automatically be updated to Saterday March 22!

                I hope this is any clearer
                • 5. Re: dateCompare and output problem
                  Dan Bracuk Level 5
                  In many databases, datefields have a time component. That will often mess people up.

                  What are you using?
                  • 6. Re: dateCompare and output problem
                    -==cfSearching==- Level 4
                    If I am understanding correctly, you should use your database's date functions instead of CF.

                    Most database's have an equivalent of CF's DateAdd function. For example the ms sql server query below would add 7 days to all records in the "events" table. Though that seems an odd requirement. You may wish to add a WHERE clause to restrict which records are updated.

                    <cfquery name="rsUpdate" datasource="Flyagain">
                    UPDATE events
                    SET eventDate = dateAdd(d, 7, eventDate)
                    </cfquery>

                    After the update, run your "rsEvents" query to get the new information and output it.