Inspiring

Answered

Query WHERE statement to compare db record date

Forum|Forum|16 years ago
June 14, 2009
6 replies
4864 views

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:

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>
                        
                        <cfset ThisDay = 0>
                        
                        <cfloop condition = "ThisDay LTE Days">
                            <tr>

                            
                            <cfloop from = "1" to = "7" index = "LoopDay">
                            
                               <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>
                            
                                    <cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
                                    <cfoutput>
                                    
                                        <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

Advanced techniques

This topic has been closed for replies.

Correct answer Dan_Bracuk

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

redundant.

T

TLC-IT

Inspiring

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.

C

CFmongerAuthor

Inspiring

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:

<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

D

Dan_Bracuk

Inspiring

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?

T

TLC-IT

Inspiring

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.

C

CFmongerAuthor

Inspiring

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

D

Dan_Bracuk

Inspiring

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.

T

TLC-IT

Inspiring

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.

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.
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.
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.

C

CFmongerAuthor

Inspiring

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:

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

<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

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

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.

<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())#">

<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>
                        
                        <cfset ThisDay = 0>
                        
                        <cfloop condition = "ThisDay LTE Days">
                            <tr>

                            
                            <cfloop from = "1" to = "7" index = "LoopDay">
                            
         <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>
                            
                                    <cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
                                    <cfoutput>
                                    
                                        <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.

T

TLC-IT

Inspiring

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.

C

CFmongerAuthor

Inspiring

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.

M

mack_

Participating Frequently

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

C

CFmongerAuthor

Inspiring

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?

D

Dan_BracukCorrect answer

Inspiring

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

redundant.

C

CFmongerAuthor

Inspiring

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

Parameter validation error for the DATEFORMAT function.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded