5 Replies Latest reply on Jun 15, 2009 11:24 AM by CFmonger

    Query WHERE statement to compare db record date pt 2

    CFmonger

      I'm sorry, I hit the answered button on my last post. I didn't mean to. I have been working on this code and have gotten closer to the solution. I just need to tweek out the WHERE statement to get rid of the time from the output, and I believe my cfif needs a little work to allow it to be taken into the actually dates. I cfdumped my variables from my query and finally got an output and not an [empty string]. This is my code now, and I will leave in the cfdump and abort tags so you can see where I put them:

       

      <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
      SELECT events.eventDate, events.ID AS ID
      FROM events
      WHERE eventDate Between #NextMonthYear# and #NextMonth# AND eventDate >= #dateFormat(Days, 'dd')#

      </cfquery>

       

      this is my cfif stement

       

      <cfoutput query="CaleventRec">
      <cfdump var="#eventDate#">
      <cfabort>
      <cfif Days EQ '#eventDate#'>
      <a href = "detail.cfm?id=#ID#">#Days#</a>
      </cfif>
      </cfoutput>

       

      this is the whole code:

       

      <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
      SELECT events.eventDate, events.ID AS ID
      FROM events
      WHERE eventDate Between #NextMonthYear# and #NextMonth# AND eventDate >= #dateFormat(Days, 'dd')#

      </cfquery>
                              <!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the first day of the month falls on is reached. --->
                              <cfset ThisDay = 0>
                              <!--- Loop through until the number of days in the month is reached. --->
                              <cfloop condition = "ThisDay LTE Days">
                                  <tr>
                                 
                                  <!--- Loop though each day of the week. --->
                                  <cfloop from = "1" to = "7" index = "LoopDay">
                                  <!--- This turns each day into a hyperlink if it is a current or future date --->
                                    <cfoutput query="CaleventRec">
                <cfdump var="#eventDate#">
                                     <cfabort>
                <cfif Days EQ '#eventDate#'>
                                     <a href = "detail.cfm?id=#ID#">#Days#</a>
                                     </cfif>
                </cfoutput>
                                     
                                  <!---

                                      If ThisDay is still 0, check to see if the current day of the week in the loop matches the day of the week for the first day of the month.
                                      If the values match, set ThisDay to 1.
                                      Otherwise, the value will remain 0 until the correct day of the week is found.
                                  --->
                                      <cfif ThisDay IS 0>
                                          <cfif DayOfWeek(ThisMonthYear) IS LoopDay>
                                              <cfset ThisDay = 1>
                                          </cfif>
                                      </cfif>
                                  <!---
                                      If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay
                                      the day of the mnth and increment the value.
                                  --->
                                          <cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
                                          <cfoutput>
                                          <!--- I choose to highlight the current day of the year using an IF-ELSE. --->
                                              <cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
                                                  <td align = "center" bgcolor="##FFFF99">
                                                      <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                  <font class = "calendartoday">#ThisDay#</font>
                                                  </td>
                                              <cfelse>
                                                  <td align = "center">
                                                      <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                  <font class = "calendar">#ThisDay#</font>
                                                  </td>
                                              </cfif>
                                          </cfoutput>
                                          <cfset ThisDay = ThisDay + 1>
                                          <cfelse>
                                              <td></td>
                                      </cfif>
                                     
                                     
                                  </cfloop>
                                  </tr>
                          </cfloop>

       

      the output from the cfdump is this: the first event date in the month of june.

      I need this to match the thisday part of the code in the cfloop function through the days of the week.

       

      Can anyone help me figure this part out?

       

      thank you, and really sorry about the 2nd post. I just woke up and hit the wrong button. again, really sorry all.

       

      CFmonger

        • 1. Re: Query WHERE statement to compare db record date pt 2
          Dan Bracuk Level 5

          This

          WHERE eventDate Between #NextMonthYear# and #NextMonth# AND eventDate >= #dateFormat(Days, 'dd')#

          is bad.

           

          I assume eventDate is a date.  However, dateformat returns a string.   Can you explain in simple english what you are trying to accomplish?   If it's selecting dates without the time portion, many db's have functions that do that.  They have names like string and to_char.  The specifics vary with the db.

          • 2. Re: Query WHERE statement to compare db record date pt 2
            CFmonger Level 1

            This is a small calendar. it will move to the next month at the end of this one, it can also go to the next month with a next / prev button that will let you go as far as you want. It is like all we have seen before. What I need to do it get the eventDate in my DB, that is set as date and put in as mm/dd/yyyy now, basically what I believe the query needs to do it to not only tell what month the calendar is on, but it needs to match any db records with any of the days in that month. Say we are in June: I have 3 records in June, on the 5th, 20th, and 26th. In all the 31 days in the month of June, just those 3 will show a link with an ID to the record that matches in the DB.

             

            I had it narrowed down with that query, but lose the variable once I try and cfif it. So I knew either my query is wrong, or my cfif is, probably both.

             

            does that help? can I make this work? am I even close?

             

            thank you.

             

             

            CFmonger

             

            By the way, here is the entire code with the next / prev nav for cycling through the months.

             

            <!--- Declaration of the variables --->
                   <cfparam name = "month" default = "#DatePart('m', Now())#">
            <cfparam name = "year" default = "#DatePart('yyyy', Now())#">
            <cfparam name = "currentday" default = "#DatePart('d', Now())#">
            <cfparam name = "startmonth" default = "#DatePart('m', Now())#">
            <cfparam name = "startyear" default = "#DatePart('yyyy', Now())#">

            <!--- Set a requested (or current) month/year date and determine the number of days in the month. --->

            <cfset ThisMonthYear = CreateDate(year, month, '1')>
            <cfset Days = DaysInMonth(ThisMonthYear)>
            <!--- Set the values for the previous and next months for the back/next links.--->
            <cfset LastMonthYear = DateAdd('m', -1, ThisMonthYear)>
            <cfset LastMonth = DatePart('m', LastMonthYear)>
            <cfset LastYear = DatePart('yyyy', LastMonthYear)>
            <cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
            <cfset NextMonth = DatePart('m', NextMonthYear)>
            <cfset NextYear = DatePart('yyyy', NextMonthYear)>

            <cfset PreviousDay = DateAdd('d', -1, ThisMonthYear)>
            <cfset CurrentYear = DatePart('yyyy', Now())>
            <table border="0" width="100%" bgcolor ="#ffffff">
                        <tr>
                            <td align = "center" valign="top">
                                <table border="0" width="100%" height="100%">
               <tr>
                  <th align="center" colspan="7" bgcolor="#2b4e6e">
               <cfoutput>
               <table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="15%" align="left" valign="middle">
              <cfif (LastYear lt CurrentYear) OR (LastYear lte CurrentYear AND LastMonth lt startmonth)>
            <cfelse>
              <a href ="index.cfm?month=#LastMonth#&year=#LastYear#" class="calNav">Prev</a></cfif></td>
                    <td width="72%" align="center" valign="middle"><FONT SIZE="3" face="Arial, Helvetica, sans-serif" color="##ffffff">#MonthAsString(month)# #year#</FONT></td>
                    <td width="13%" align="right" valign="middle">
              <cfif (NextYear lt CurrentYear) OR (NextYear lte CurrentYear AND NextMonth lt startmonth)>
            <cfelse>
            <a href = "index.cfm?month=#NextMonth#&year=#NextYear#" class="calNav">Next</a>  </cfif></td>
                  </tr>
                </table></cfoutput></th>
               </tr>
               <tr>
              
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sun</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Mon</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Tue</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Wed</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Thu</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Fri</FONT></td>
                <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sat</FONT></td>

               </tr>
            <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
            SELECT events.eventDate, events.ID AS ID
            FROM events
            WHERE eventDate Between #NextMonthYear# and #NextMonth# AND eventDate >= #dateFormat(Days, 'dd')#
            </cfquery>
                                    <!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the first day of the month falls on is reached. --->
                                    <cfset ThisDay = 0>
                                    <!--- Loop through until the number of days in the month is reached. --->
                                    <cfloop condition = "ThisDay LTE Days">
                                        <tr>
                                       
                                        <!--- Loop though each day of the week. --->
                                        <cfloop from = "1" to = "7" index = "LoopDay">
                                        <!--- This turns each day into a hyperlink if it is a current or future date --->
                     <cfoutput query="CaleventRec">
                      <!---  <cfdump var="#eventDate#">
                                           <cfabort>--->
                      <cfif #dateFormat(eventDate, 'dd')# IS ('Days, LoopDay')>
                      <cfdump var="#eventDate#">
                                           <cfabort>
                                           <a href = "detail.cfm?id=#ID#">#Days#</a>
                                           </cfif>
                      </cfoutput>
                                           
                                        <!---

                                            If ThisDay is still 0, check to see if the current day of the week in the loop matches the day of the week for the first day of the month.
                                            If the values match, set ThisDay to 1.
                                            Otherwise, the value will remain 0 until the correct day of the week is found.
                                        --->
                                            <cfif ThisDay IS 0>
                                                <cfif DayOfWeek(ThisMonthYear) IS LoopDay>
                                                    <cfset ThisDay = 1>
                                                </cfif>
                                            </cfif>
                                        <!---
                                            If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay
                                            the day of the mnth and increment the value.
                                        --->
                                                <cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
                                                <cfoutput>
                                                <!--- I choose to highlight the current day of the year using an IF-ELSE. --->
                                                    <cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
                                                        <td align = "center" bgcolor="##FFFF99">
                                                            <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                        <font class = "calendartoday">#ThisDay#</font>
                                                        </td>
                                                    <cfelse>
                                                        <td align = "center">
                                                            <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                        <font class = "calendar">#ThisDay#</font>
                                                        </td>
                                                    </cfif>
                                                </cfoutput>
                                                <cfset ThisDay = ThisDay + 1>
                                                <cfelse>
                                                    <td></td>
                                            </cfif>
                                           
                                           
                                        </cfloop>
                                        </tr>
                                </cfloop>
                                </table>
                            </td>
                        </tr>
                    </table>

             

             

            • 3. Re: Query WHERE statement to compare db record date pt 2
              Dan Bracuk Level 5

              This is how I get data for the current month

               

              StartDate = CreateDate(year(now()), month(now()), 1);

              EndDate = DateAdd("m", 1, StartDate);

               

               

              select somefields

              from sometables

              where somedatefield >= #StartDate# and somedatefield < #EndDate#

              • 4. Re: Query WHERE statement to compare db record date pt 2
                CFmonger Level 1

                ok. si If i make this into cfsets, that will probably get my month issue,What I am thinking is that I am trying to compair a number to a date. The calendar being the number, and the db using the actual date..

                 

                If we use this, and filter out the month of june from the records, how do we go to the day (dd) and make it into a number that we can compair to the days of the month?So lets say this is one part of half the problem.

                right now, it seems the query I am using, that is tied into the next / prev function, that is right now, taking june records, but I am not getting it to convert the day into a number.

                • 5. Re: Query WHERE statement to compare db record date pt 2
                  CFmonger Level 1

                  I have been reading live docs and I found this date compairison tag. I was working on tweeking this up to see if I could use this to solve the date problem. It is throwing an error, and was wondering if this wold help to finish this off and compair the date in the db and the date in a calendaer.This is how i set it up:

                   

                  This is the tag to compare: <cfset comparison = DateCompare(#eventDate#, #Days#, d)>

                   

                  <!--- This is part of the next / prev for being able to cycle through the month--->

                  <cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
                  <cfset NextMonth = DatePart('m', NextMonthYear)>

                   

                  <!--- this is the query --->

                  <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
                  SELECT events.eventDate, events.ID AS ID
                  FROM events
                  WHERE eventDate Between #NextMonthYear# and #NextMonth#
                  </cfquery>

                   

                  <!--- this is where I compair the event date to the day, this is where my error is right now--->

                  <cfoutput query="CaleventRec">
                           <cfset comparison = DateCompare('#dateFormat(eventDate, 'dd')#', '#Days#', 'd')>
                  <!--- if I do a cfdump here, I get the record for this month, in the day as a number in this case 25 for the 25th--->

                         

                  <cfif #Days# EQ ('#dateFormat(eventDate, 'dd')#')>
                            <!--- when I do this dump, it doesn't fire, so my if statement is incorrect--->

                                          <cfdump var="#eventDate#">
                                                 <cfabort>
                                                 <a href = "detail.cfm?id=#ID#">#Days#</a>
                                                 </cfif>
                            </cfoutput>

                   

                  This is the error I am getting:

                   

                  Variable D is undefined.

                  The error occurred in C:\website\event\index.cfm: line 169
                  167 :                             <!--- This turns each day into a hyperlink if it is a current or future date --->
                  168 :                                      <cfoutput query="CaleventRec">
                  169 :                                      <cfset comparison = DateCompare(#eventDate#, #Days#, d)>

                   

                  The D is supposed to narrow the comparison down to the exact day. well, that's what I got from reading the live docs.
                  Can anyone help me make this work?Thanks

                   

                  CFmonger

                   

                  Quick Update:I have it dumping the day as a number up to my cfif statement, in the proper month. Can someone help me get it farther? I need to lopp through the records for all the records that match up for the month they are looking at.

                  I corrected the code to get it doing where it is now. The error is not happening anymore. But my cfif statement is not working properly.
                  Can anyone help please.