Trouble with a simple Query

[noob28]

Hello, I'll start by saying that I am a noob. Anyways, I am trying to do what I thought would be a simple query to get records that are greater than or equal to the current date: this is my query...

<cfquery name="getUpcoming" datasource="events">
SELECT title, eventDate FROM event WHERE eventDate >= #Now()# ORDER BY eventDate ASC
</cfquery>

It works, sort of, I do get records that are greater than the current date, but any records that are equal to do not show up.

I am assuming that it is looking at time as well, or I am doing it completely wrong. I don't know? Any help would be greatly appreciated.

[Guest]

Plz try something like this..

<cfset CheckDate =dateformat(Now(),"dd-mm-yyyy")>
<cfset CheckDate = CheckDate & " 00:00:00">
<cfset CheckDate =dateformat(CheckDate,"dd/mmm/yyyy") & " 00:00:00" >
<cfdump var="#CheckDate#">
<cfquery name="qry_test" datasource="#application.dsn#">
   select * from  event where
    timedate >=#createodbcdatetime(CheckDate)#
    
   </cfquery>
<cfdump var="#qry_test#">

[noob28]

Thanks for the reply, I just get an error,

Variable QRY_TEST is undefined

[Guest]

Plz verify whether u changed ur query name to QRY_TEST or dump the query name u r using..

[noob28]

Yes, I did change it. I tried removing it and using cfoutput, but I got another error "Column not found: Unknown column 'timedate' in 'where clause'"

[existdissolve]

In your original post, the column name was "eventdate"--did you change it to "timedate" in the db?  It looks like the person who first responded changed the column name from eventdate to "timedate" in their example...did you just copy and paste their example into your code?

[noob28]

I just created a new page to test out his example, I did not change that value. Thanks.

[-__cfSearching__-]

Never mind. I see you have got it now.

Message was edited by: -==cfSearching==-

[Adam Cameron. · Accepted solution]

Have a read up on <cfqueryparam> (http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html), which you should be using when you are passing dynamic values to the DB, instead of hard-coding them into your SQL string.  And when using <cfqueryparam>, use a param type of CF_SQL_DATE, and that should ignore the time part of the date/time value you pass in (I am not 100% certain of this... only about 99% certain... but give it a go).  Or only pass the date part of your date/time value into the query.  Using createOdbcDate() does this, without all the extra horsing around appending things together and using dateFormat() that the previous poster suggested.

--

Adam

[Dan_Bracuk]

There might be a simpler answer yet, depending on the db you are using.  If it has a function that returns the current date, use that function.

[noob28]

Thanks, that worked perfectly. I just changed it to this

<cfquery name="getUpcoming" datasource="events">
SELECT title, eventDate FROM event WHERE eventDate >= #(createODBCDate(Now())# ORDER BY eventDate ASC
</cfquery>

I didn't use the cfqueryparam as suggested, is there something dangerous about doing it this way?

[Adam Cameron.]

I didn't use the cfqueryparam as suggested, is there something dangerous about doing it this way?

Nothing dangerous, no.  Just "less than ideal" (in a sloppy / lazy sort of way).  As I suggested, one should not hard-code dynamic values into the SQL string, one should pass them as parameters.  it's just "the way it should be done".

When the DB receives your SQL string (with the dynamic values hard-coded), the DB engine needs to compile the SQL to make an execution plan before executing the query.  Any change to the SQL string requires recompilation.  However if you pass your parameter as parameters, then the SQL does not need to be recompiled.

It's the same sort of thing as not using global variables unless one has to, despite the fact they're "easier", or duplicating code instead of refactoring code.  One should try to write decent code.

--

Adam

[noob28]

Ok, thanks. I may implement that later, I don't think it will be much of a performance

issue, it is the simplest DB, one table, does one thing, on one page,and thats it. Thanks again for all the help!

[josh_adams1]

Yes, it is true, strictly speaking, that there is no danger in not using cfqueryparam in this specific case, however, there is danger in falling into the practice of ever not using it: if you get into the practice of always using it then you'll be much less likely to ever forget to use it and thereby accidentally forget to use it when you need it for defeating SQL injection attacks on your database. So: always use cfqueryparam when sending data with SQL queries and keep yourself out of trouble!

(Ssome advanced developers may argue that there are a few edge cases where for data that is known to be safe it's better to not use cfqueryparam because in these particular few edge cases it has been determined that cfqueryparam actually decreases performance rather than increases it as is the general case.  I'm not against that, but remember:  it's an edge case!  In the general case, the rule remains always use cfqueryparam!)

[Dan_Bracuk]

There is nothing dangerous about doing it that way but it is inefficient because you are running an extra function.  Also, cfqueryparams tend to make queries run faster.