23 Replies Latest reply on Jun 18, 2009 1:30 PM by CFmonger

    Query WHERE statement to compare db record date

    CFmonger Level 1

      Hello

      I can't get this where statement to work. I have it now so it is not throwing any errors, but it also isn't doing what it is supposed to.
      I am trying to match records date in my table with the actual calendar date, if there is a match, my cfif will take that match, and make it a link in the proper date. (it is a small calendar with the month, and the days of week with dates, each date will be normal, accept if there is a record for that date, it makes a link.

       

      This is my code, like I said, It doesn't throw an error, but it is not working either.

       

      My Query:

       

      <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
      SELECT events.eventDate, events.ID AS ID
      FROM events
      WHERE eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#
      </cfquery>

       

      This is the cfif statement:

       

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

       

      this is all the code together:

       

      <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
      SELECT events.eventDate, events.ID AS ID
      FROM events
      WHERE eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#
      </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">
                <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>

       

       

      I know my where statement is incorrect, I am also not sure if my cfif statement is correct either. Can anyone help me figure this out?

      thank you!

      CFmonger

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

          Start by dumping the query result and see if you get what you expect to get.  If not, look at the value of your days variable.

           

          By the way, this

          AND eventDate = #Days#

          makes this

          <cfif Days EQ '#eventdate#'>

          redundant.

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

            Ok, I dumped the query and changed the way I coded it a little and now I get [empty string] as a result of my dump of my variable 'eventDate'. So my where statement is not correct. What do I need to change this where statement to so it will match records and use an if statement to make it work?

             

            I didn't mean to click on the button to say this was answered.... accident. sorry

            • 3. Re: Query WHERE statement to compare db record date
              mack_ Level 3

              That's too much code to read for me but one thing that I noticed is

              that Days is a number and eventDate is a date and you're comparing

              them.

               

              Mack

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

                That is what I am trying to do, compair the date in eventDate to the number date in the calendar, how to I just compair the 2 as numbers? It also need to make sure it is in the same month that the records appear in, if there are 5 records for june, they link in the 5 dates in the month, in july same thing, as you cycle through the months, if there are records for that month, they appear as links.Is it possible to do this?

                • 5. Re: Query WHERE statement to compare db record date
                  TLC-IT Level 2

                  You probably don't mean to say this:

                   

                  eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#

                   

                  Undoubtedly you intended to use a different variable in the second case.

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

                    what code do I write to make this work? That is my question.In my other post it is currently what I am using, I have to not only sort the records for the month, but sort through the days and match them to a day of the week if they fall in the month. How do I make this happen? It is a little over my head and really would like to understand it.

                    • 7. Re: Query WHERE statement to compare db record date
                      TLC-IT Level 2

                      I do not clearly visualize what you are trying to do here, and I daresay no one else does either.  The comments are simply "running commentary," and the variable names really don't imply anything.  So, I suggest that the best thing to do here is ... start over.

                       

                      1. Preferably using a separate database-specific tool, get your query down first.  All SQL databases have a full complement of date/time functions which can be used to extract any desired component(s) from a date, e.g. month-number, day-number and so on.  Make sure that the query you come up with produces all of these derived numbers as you need them ... as ordinary integer columns in the result.
                      2. Remember that you can use the date/time functions in a WHERE clause, as well.  So, you might need something like DAYOFMONTH(eventDate) = #Days# or somesuch.  There should be no string-twiddling here.
                      3. Once you've got the query producing the results that you want, in the order that you want, work out the algorithm on paper.  Then, you can take another stab at re-implementation.

                       

                      I do not mean this response to be flippant:  sometimes you just get a first-draft piece of code "all bolluxed up" when you go whacking at it "trying to get the damn thing to work."  Frankly, this snippet of code looks pretty well whacked-out by now.  So you need to step well away from the computer and get a clear mental image of what you want to do here.  Throw away the code that you have.  Clear you head and try again.

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

                        All the rest of the code works. The only part of this that doesn't work is the trying to make a link part.
                        Let me  show you where I am, and what I did, then maybe you will see I am closer to the solution... I think. I made this calendar so you can cycle through the months. So if you are looking at June, you see Sun - Sat and the dates for each day of the week in the month of june. You can then choose to click the next button that will bring up the month of july, and you can keep on going as long as you want.

                         

                        That is one function and it working as of right now. This Calendar also shows what day of the week we are on. If it is June 16th then June 16 is yellow. This also works.NOw, what I need to do it to have my query, not only filter out the records for the month, (if we are in June, then the Query separates out the records for June, the date in my DB is set to mm/dd/yyyy) This query also needs to match it to a number, the days of the month. So, if there is a record on the 25th of June, then the 25th of June will be a link to another page using it's ID in the address line. If you go to July and there are lets say 3 records for that month, then all 3 dates will become a link to the same other page using it's ID in the url.

                         

                        This is what I have now. I tied the Query into the next / prev function so it filters the month, so far it seems to work, I can do a cfdump up to the point of my cfif statement and get the proper number from the date, my cfif is not firing so If I do a dump after it, it doesn't dump anyhting.

                         

                        I will post JUST the query code, then post the whole calander code so you can see it all together.

                         

                        The Query / link code:

                         

                        <!--- This is part of the next / prev nav to cycle through the months --->

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

                        <!--- My Query --->

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

                         

                        <!--- my output / cfif stement --->

                        <cfoutput query="CaleventRec">
                        <cfset comparison = DateCompare('#dateFormat(eventDate, 'dd')#', '#Days#', 'd')>
                        <!--- When I do a cfdump here, I get the number of the first record with a day date in the DB for the month of june

                        <cfdump var="#dateFormat(eventDate, 'dd')#">
                                                       <cfabort>--->
                        <cfif #Days# EQ ('#dateFormat(eventDate, 'dd')#')>

                        <!--- When I do a dump here, I do not get anyhting, no numbers it doesn't fire

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

                         

                        ok, now that is the part to stream in the DB info into the calander.

                        Here is all the code, like I said, it all works, just not making each day a link that has a record in the DB.

                         

                        <!--- 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#
                        </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">
                                 <cfset comparison = DateCompare('#dateFormat(eventDate, 'dd')#', '#Days#', 'd')>
                                  <cfdump var="#dateFormat(eventDate, 'dd')#">
                                                       <cfabort>
                                  <cfif #Days# EQ ('#dateFormat(eventDate, 'dd')#')>
                                  <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>

                         

                        It is kind of complicated and this is why I need help. Maybe I am wrong in the variables I am pulling, but like I say in my comments, so far I pulled the day part of the month I am in out of the records up until my cfif stement.Can this be done with what I have done so far? No need to rewrite the whole calander, just the part with the query if I miss what you are saying.

                        • 9. Re: Query WHERE statement to compare db record date
                          TLC-IT Level 2

                          I'd approach the problem this way:

                           

                          • At any one time you are looking for all of the entries of a particular month and year.  Therefore, in your query, use the MONTH() and YEAR() functions as part of your WHERE clause:

                           

                           

                          WHERE YEAR(EventDate)  = #WhatYear#

                            AND MONTH(EventDate) = #WhatMonth#

                           

                          ... or if you prefer, something like YEAR(EventDate) = YEAR(#date_of_interest#), etc.

                           

                                 ORDER BY EventDate ASC

                           

                           

                          • Now, let's dispense with that pesky "line-printer logic."  Set up a CF struct and use it like a sparse array to capture whatever records might be there.  Fill the struct, then close the query-cursor.

                           

                          • If you need more than one event per day, each element of the struct should be a list, not a scalar.

                           

                          • Now you are left with a loop that will be performed for each day-number in the range 1-31.  Or, maybe it's a nested loop for 7 columns and up to 5 rows.  If there is a tag for a particular day, then there's an event there.  Fill in the table-cell, giving it the yellow color as appropriate.  CF will tell you the day-number for any date, so you know on what day the month starts... and you can leave that many empty slots at the beginning of the calendar.  Likewise, it tells you how many days are in a month, so you also know when to leave blank cells on the last row.

                           

                          • The implementation of the "next" and "previous" buttons is obvious:  subtract one or add one, and adjust for year-wraparound.

                           

                          The appeal, to me, of this strategy is that I understand it.  And the person who comes after you will too.  Notice the "separation of concerns" here:  I didn't mix the process of building the calendar with the process of determining what should go in it.  I like to design code that way.

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

                            I understand what you are saying:

                            <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
                            SELECT events.eventDate, events.ID AS ID
                            FROM events
                            WHERE YEAR(EventDate)  = #NextMonthYear#

                            AND MONTH(EventDate) = #NextMonth#
                            </cfquery>

                             

                            (Not sure if those are the variables I should be grabbing)
                            I don't mean to seem like I am trying to get someone else to do my work, but how would I write this as an if statement that will work?

                             

                            I totally understand what you are saying, but do not know how to write it.CFmonger

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

                              That sql will probably give you what you want.  It might not be the most efficient way to do it.  If your table has a lot of records, it will be slow because you are using functions in your where clause.

                               

                              The sql I gave you in the other thread is logically equivalent to this sql.  However, if your table has a lot of records, and EventDate is indexed, my sql will be faster.

                               

                              Also, you still have to strip the time part away from the EventDate.  You have already been told how.

                              • 12. Re: Query WHERE statement to compare db record date
                                TLC-IT Level 2

                                It's now time for you to take a stab at it, based on what you now know.  Get a clear picture in your head of what you are shooting for, then, dive in.  It's the only way.

                                 

                                You know that you're looking for records where the event-date is this year and this month (not "next year/month").

                                 

                                This will give you the set of events that you need to display somewhere on the calendar for that month.  Accumulate them in a data-structure, as I said, then separately build the table.

                                 

                                Let go of the code that you now have.  A replacement that will be much clearer and understandable will occupy about two pages of source-code.  Time to get off the edge of the pool and get back into the water.

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

                                  I am working out a new query and it's own rules, so it will, hopfully slip right in. This is what I have started. right now it is throwing an error, I am missing an operator in my query. Can you help me figure out what I missed, and actually, am I on the right path?

                                   

                                  New Query:

                                   

                                  <cfset StartDate = CreateDate(year(now()), month(now()), 1)>
                                  <cfset EndDate = DateAdd("m", 1, StartDate)>

                                   

                                  <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
                                  SELECT events.eventDate, events.ID AS ID
                                  FROM events
                                  WHERE YEAR(eventDate)  = #DateFormat(StartDate, "mm/dd/yy")#
                                    AND MONTH(eventDate) = #DateFormat(EndDate, "mm/dd/yy")#
                                  ORDER BY MONTH(eventDate) ASC
                                  </cfquery>

                                   

                                  The Error:

                                  Parameter validation error for the DATEFORMAT function.

                                  The value of parameter 1, which is currently StartDate, must be a class java.util.Date value.
                                  The error occurred in C:\website\event\index.cfm: line 160
                                  158 : SELECT events.eventDate, events.ID AS ID 
                                  159 : FROM events 
                                  160 : WHERE YEAR(eventDate)  = #DateFormat('StartDate', "mm/dd/yy")#
                                  161 :   AND MONTH(eventDate) = #DateFormat('EndDate', "mm/dd/yy")#
                                  162 : ORDER BY MONTH(eventDate) ASC
                                  

                                   

                                  what do you think? Is this a good start?

                                   

                                  thanks

                                   

                                  CFmonger

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

                                    You are making a couple of errors.  Here:

                                    WHERE YEAR(eventDate)  = #DateFormat(StartDate, "mm/dd/yy")#
                                      AND MONTH(eventDate) = #DateFormat(EndDate, "mm/dd/yy")#

                                     

                                    you are comparing numbers to strings.  It won't work.

                                     

                                    The error message you are getting shows different code than you posted.  Where did those single quotes come from?

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

                                      I am attempting to rewrite the query,I made it up like you had posted, I get a different error:

                                       

                                      this is how I have the query now:

                                      SELECT events.eventDate, events.ID AS ID
                                      FROM events
                                      WHERE eventDate >= #StartDate# and eventDate < #EndDate#
                                      ORDER BY eventDate ASC

                                       

                                      this is the error:

                                       

                                      Error Executing Database Query.

                                      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
                                      The error occurred in C:\website\event\index.cfm: line 160
                                      158 : SELECT events.eventDate, events.ID AS ID 
                                      159 : FROM events 
                                      160 : WHERE eventDate >= #StartDate# and eventDate < #EndDate#
                                      161 : ORDER BY eventDate ASC
                                      162 : </cfquery>
                                      

                                      SQLSTATE  07002
                                      SQL   SELECT events.eventDate, events.ID AS ID FROM events WHERE eventDate >= {ts '2009-06-01 00:00:00'} and eventDate <{ts '2009-07-01 00:00:00'} ORDER BY eventDate ASC
                                      VENDORERRORCODE  -3010
                                      DATASOURCE  mainDB
                                      Resources:

                                       

                                      I take it I need to do a dateformat function someplace in here? to cut it down.

                                      do I put it on the WHERE eventDate?

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

                                        Your error message may be misleading.  You might be using a reserved word, ID, as a column alias.  Take it away and see if you still get the error.

                                         

                                        Another possible source would be the mismatch between the select clause, events.eventdate, and the order by clause, eventdate.  Stranger things have happened.

                                         

                                        Regarding dateformat, that returns a string, so if you want to use it, you have to get a string from your db to compare it with.  It would be a lot simpler to use month() and year(), but I'm not sure why you have to add anything to your where clause.

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

                                          I have it down to a syntax error now.

                                           

                                          this is what I am working on:

                                           

                                          <cfset StartDate = CreateDate(year(now()), month(now()), 1)>
                                          <cfset EndDate = DateAdd("m", 1, StartDate)>

                                          <cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
                                          SELECT MONTH(eventDate), events.ID AS ID
                                          FROM events
                                          WHERE MONTH(eventDate) >= #StartDate# AND MONTH(eventDate) <= #EndDate#
                                          </cfquery>

                                           

                                          this is the error:

                                          Error Executing Database Query.

                                          [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'YEAR(eventDate) >= #6/1/2009 00:00:00# AND YEAR(eventDate) <= #7/1/2009 00:00:00#'.
                                          The error occurred in C:\website\event\index.cfm: line 160
                                          158 : SELECT YEAR(eventDate), events.ID AS ID 
                                          159 : FROM events 
                                          160 : WHERE YEAR(eventDate) >= #StartDate# AND YEAR(eventDate) <= #EndDate#
                                          161 : </cfquery>
                                          

                                           

                                          think we are close! Any ideas?

                                          next question is how to write the if statement.

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

                                            I worked up this code most of the night. I have all new code and this should work. I ripped it off another calendar but I am again back to my cfif statement not firing. I know it is because the date in the db is read as mm/dd/yyyy and right now my code is not reading it that way.

                                             

                                            here is the new code:

                                             

                                            <cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
                                            <!--- Add new variable for the end of the month--->
                                            <cfset endOfTheMonth = createdatetime(year(now()), month(now()), #daysinmonth(firstOfTheMonth)#, 23, 59, 59)>

                                             

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

                                             

                                            <cfset day = ValueList(CaleventRec.eventDate)>

                                             

                                            <cfoutput query="CaleventRec">

                                            <cfif listFind(day,LoopDay)>
                                            <a href = "detail.cfm?id=#ID#">#Days#</a>
                                            </cfif>
                                            </cfoutput>

                                             

                                            There has to be a way to make this work! I have pspent weeks on this thing and for some reason no one seems to know how to do it?! Come on! Please help mne get this fixed it really can't be that hard. I have tried a ton of different approaches and find it hard that someone doesn't know how to make this work.Please help.

                                             

                                            Thank you

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

                                              If loopday is a date variable, you can change this:

                                               

                                              <cfset day = ValueList(CaleventRec.eventDate)>

                                               

                                              <cfoutput query="CaleventRec">

                                              <cfif listFind(day,LoopDay)>
                                              <a href = "detail.cfm?id=#ID#">#Days#</a>
                                              </cfif>
                                              </cfoutput>

                                               

                                              to this

                                               

                                              <cfoutput query="CaleventRec">

                                              <cfif Dateformat(loopday, "mask") is DateFormat(eventdate, "mask")>
                                              <a href = "detail.cfm?id=#ID#">#Days#</a>
                                              </cfif>
                                              </cfoutput>

                                               

                                              The Dateformat takes care of the time portion of your db value

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

                                                the cfif is still not responding. I can do a dump before the cfif still and when I dump it after teh tag, it doesn't do anything. This is driving me crazy.....

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

                                                  I solve if/else problems like this:

                                                   

                                                  <cfif a is b>

                                                  yes

                                                  <cfelse>

                                                  cfdump a and b

                                                  </cfif>

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

                                                    just tried that, it does the dump after the cfelse not the other. I know ther eis a record in this month that should make a link. I put it there on purpos.

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

                                                      I took the advice of chuck it and start over. I had success with this, so i am going to start a new threat. This one has gotten too jumbled up and think it is best to look at it totally differently.I am sorry for the double posting on this.