Query WHERE statement to compare db record date

Report · Jun 14, 2009

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

Report · Jun 14, 2009

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.

Report · Jun 14, 2009

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

Report · Jun 15, 2009

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

Report · Jun 15, 2009

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?

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

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

Report · Jun 16, 2009

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?

Report · Jun 16, 2009

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?

Report · Jun 16, 2009

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.

Report · Jun 16, 2009

I have it down to a syntax error now.

this is what I am working on:

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

Report · Jun 17, 2009

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:

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

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

Report · Jun 17, 2009

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

to this

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

Report · Jun 17, 2009

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

Report · Jun 17, 2009

I solve if/else problems like this:

yes

cfdump a and b

</cfif>

Report · Jun 17, 2009

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.

Report · Jun 18, 2009

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.

Adobe Community

Query WHERE statement to compare db record date

1 Correct answer

Parameter validation error for the DATEFORMAT function.

Error Executing Database Query.

Error Executing Database Query.