Trouble with a simple Query

Forum|Forum|15 years ago
September 20, 2010
2 replies
1358 views

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.

This topic has been closed for replies.

Correct answer Adam Cameron.

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

A

Adam Cameron.Correct answer

Inspiring

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

D

Dan_Bracuk

Inspiring

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.

A

Anonymous

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

N

noob28Author

Participating Frequently

Thanks for the reply, I just get an error,

Variable QRY_TEST is undefined

A

Anonymous

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

Variable QRY_TEST is undefined

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