SQL Query Problem: BETWEEN 1

[brian1313]

I've been having a problem with an events display page for a college performing group. We want to sort the records by semester and year from the date field and that's been taken care of. The problem is displaying them dynamically.

Here's the query:

<cfquery name=&quot;events2&quot; datasource=&quot;ksuperc&quot; debug>
SELECT * FROM events WHERE month(event_date)
<cfif url.sem IS 1>
BETWEEN 9 AND 12
<cfelseif url.sem IS 2>
BETWEEN 5 AND 8
<cfelse>
BETWEEN 1 AND 4
</cfif>
AND (year(event_date) = '#url.yr#')
</cfquery>

This query only returns a recordcount for the Summer semester (url.sem = 2.) The Fall and Spring semesters (url.sem = 1 and the cfelse statements respectively) are not retunring any records even though there are records that should be displayed for Spring and Summer 2003, Fall 2002, Summer and Fall 2001.

There's got to be a way to make this query work, isn't there? Thanks in advance...

-b-

 

[brian1313]

nope, never mind... i was using the wrong value... fall was 3, not 1... i'm a putz...

If anyone sees any problems though, please let me know

-b-

 

[r937]

the query will run (i.e. not give a datatype mismatch error), or will run quicker (i.e. not need to do a datatype conversion), if you don't put #url.yr# in quotes

also, you may already have this and just not included it in the code snippet you posted, but it's a good idea to have the following ahead of the first use of the url variables

<CFPARAM NAME=&quot;url.sem&quot; TYPE=&quot;numeric&quot; DEFAULT=&quot;3&quot;>
<CFPARAM NAME=&quot;url.yr&quot; TYPE=&quot;numeric&quot; DEFAULT=&quot;2002&quot;>

rudy

 

[brian1313]

OK. This really is a question, it's just really long. Let's see. I'm using this:

<cfif month(Now()) GTE 9 AND month(Now()) LTE 12>
<cfquery name=&quot;events2&quot; datasource=&quot;ksuperc&quot;>
SELECT * FROM events WHERE year(event_date) = year(Now()) AND month(event_date) BETWEEN 9 and 12
</cfquery>
<cfelseif month(Now()) GTE 5 AND month(Now()) LTE 8>
<cfquery name=&quot;events2&quot; datasource=&quot;ksuperc&quot;>
SELECT * FROM events WHERE year(event_date) = year(Now()) AND month(event_date) BETWEEN 5 and 8
</cfquery>
<cfelse>
<cfquery name=&quot;events2&quot; datasource=&quot;ksuperc&quot;>
SELECT * FROM events WHERE year(event_date) = year(Now()) AND month(event_date) BETWEEN 1 and 4
</cfquery>
</cfif>

in conjunction with

<cfif IsDefined(&quot;url.sem&quot>

to select everything scheduled six months prior if the URL isn't defined. Instead of running a <cfif IsDefined(&quot;url.sem&quot> to determine which of two different but repetitive sections of code to use, I can dynamically populate the <cfparam> statement based off a Now() function and some neato little scripting, right?

I can code itwithout problems if the answer is yes.

-b-

 

[r937]

yes