?
Solved

Coldfusion / SQL  - Search Month Range in DB from form input

Posted on 2009-04-16
7
Medium Priority
?
935 Views
Last Modified: 2012-06-21
Hi,

I have a form setup in which a user can pick a month, click submit, the query searches for everything in that month.  Problem is, it's searching and returning everything regardless of the month (dates) input.  I've place the code below.  The problem is in the and       getBreakouts.time_start Between #MonthStart# and #MonthEnd# statement.

I know it may be a bit hard to tell what's going on b/c you can't see the db ~ it's an oracle db and date fields are legit date fields.

Thanks tons for any help!
<form action="index.cfm?pageid=#request.pageid#" method="post" name="form1">
<tr>
      <td>Select Month</td>
	  <td>
	  	<cfparam name="CurntDate" default="#now()#">
		<cfparam name="YearSet" default="#year(now())#">
		
	    <cfset monthSet = ArrayNew(1)>
        <cfset monthSet[1] = "January">
        <cfset monthSet[2] = "February">
        <cfset monthSet[3] = "March">
        <cfset monthSet[4] = "April">
        <cfset monthSet[5] = "May">
        <cfset monthSet[6] = "June">
        <cfset monthSet[7] = "July">
        <cfset monthSet[8] = "August">
        <cfset monthSet[9] = "September">
        <cfset monthSet[10] = "October">
        <cfset monthSet[11] = "November">
        <cfset monthSet[12] = "December">
        <select name="month">
          <option value="" selected>
          <cfloop index="i" from="1" to="12">
		  
		  <cfif i LT 10>
		  <cfset ldate = DateFormat(CurntDate,"2009/0#i#/dd")>
		  <cfelse>
		  <cfset ldate = DateFormat(CurntDate,"2009/#i#/dd")>
		  </cfif>
		  
            <option value="#ldate#">#monthSet[i]# #YearSet# - value = #ldate#</option>
          </cfloop>
        </select>
		(Leave Blank for All)</td>
    </tr>
<tr>
<td></td><td><input type="Submit" name="view" value="View" class="btn" >
 
</td>
</tr>
<input type="Hidden" name="sendinfo" value="1">
</form>
 
 
 
 
 
<cfif len(trim(form.month))>
<CFSET curntdate = createodbcdate(form.month)>
 
<CFPARAM name="YearSet" default="#year(now())#">
<CFPARAM name="MonthSet" default="#month(now())#">
 
<!--- Create the date variables to search the Database by. --->
<CFSET monthstart = createdatetime(#yearset#, #monthset#, 1, 0, 0, 0)>
<CFSET monthend = createdatetime(#yearset#, #monthset#, #daysinmonth(monthstart)#, 23, 59, 59)>
</cfif>
 
<cfquery dbtype="query" name="getBreakouts">
	select 	getBreakouts.*,
			getClasses.class_pts
	from 	getBreakouts,
			getClasses
	where	1=1
	<cfif len(trim(form.month))>
	and 	getBreakouts.time_start Between #MonthStart# and #MonthEnd#
	</cfif>
	and		getBreakouts.CLASS_ID = getClasses.id	
</cfquery>

Open in new window

0
Comment
Question by:wkolasa
  • 6
7 Comments
 
LVL 19

Assisted Solution

by:erikTsomik
erikTsomik earned 80 total points
ID: 24158141
can you try this instead
getBreakouts.time_start >=#MonthStart# and getBreakouts.time_start <= #MonthEnd#
0
 

Author Comment

by:wkolasa
ID: 24158157
I'll give that a shot
0
 

Author Comment

by:wkolasa
ID: 24158469
There's a bigger issue:  Date value passed to date function CreateDateTime is unspecified or invalid.  
Specify a valid date in CreateDateTime function.  


Apparently it doesn't like the way I'm sending the date over
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wkolasa
ID: 24158528
How's this for an error:

Query Of Queries runtime error.  
Comparison exception while executing >=.
Unsupported Type Comparison Exception: The >= operator does not support comparison between the following types:
Left hand side expression type = "TIMESTAMP".
Right hand side expression type = "DOUBLE".
 
 
0
 

Author Comment

by:wkolasa
ID: 24158592
Okay, I'm making everything simpler but need help doing it.  How do I search all dates within a month.  So, I submit 2009/04/16  -  I want all rows that are in the month of April.  Essential, can I just submit the month and then write a statement saying, "Here the month, figure it out." ??
0
 

Author Comment

by:wkolasa
ID: 24158969
I rewrote everything and code is below.  I'm receiving this error now:

Query Of Queries runtime error.  
Comparison exception while executing BETWEEN.
Unsupported Type Comparison Exception: The BETWEEN operator does not support comparison between the following types:
Left hand side expression type = "TIMESTAMP".
Right hand side expression type = "LONG".

<form action="index.cfm?pageid=#request.pageid#" method="post" name="form1">
<tr>
      <td>Select Month</td>
	  <td>
	  	<cfparam name="CurntDate" default="#now()#">
		<cfparam name="YearSet" default="#year(now())#">
		
	    <cfset monthSet = ArrayNew(1)>
        <cfset monthSet[1] = "January">
        <cfset monthSet[2] = "February">
        <cfset monthSet[3] = "March">
        <cfset monthSet[4] = "April">
        <cfset monthSet[5] = "May">
        <cfset monthSet[6] = "June">
        <cfset monthSet[7] = "July">
        <cfset monthSet[8] = "August">
        <cfset monthSet[9] = "September">
        <cfset monthSet[10] = "October">
        <cfset monthSet[11] = "November">
        <cfset monthSet[12] = "December">
        <select name="month">
          <option value="" selected>
          <cfloop index="i" from="1" to="12">
		  
		  <cfif i LT 10>
		  <cfset ldate = DateFormat(CurntDate,"2009/0#i#/dd")>
		  <cfelse>
		  <cfset ldate = DateFormat(CurntDate,"2009/#i#/dd")>
		  </cfif>
		  
            <option value="#ldate#">#monthSet[i]# #YearSet# - value = #ldate#</option>
          </cfloop>
        </select>
		(Leave Blank for All)</td>
    </tr>
<tr>
<td></td><td><input type="Submit" name="view" value="View" class="btn" >
 
</td>
</tr>
<input type="Hidden" name="sendinfo" value="1">
</form>
 
<cfif isDefined('form.month')>
	<cfset dtThisMonth = CreateDate(year(#form.month#),month(#form.month#),1) >
    <cfset dtNextMonth = DateAdd("m",1,dtThisMonth) >
    <cfset dtEndMonth = DateFormat(dtnextMonth-1, "mm-dd-yyyy")>
    
</cfif>
 
<cfquery dbtype="query" name="getBreakouts">
    select 	getBreakouts.*,
            getClasses.class_pts
    from 	getBreakouts,
            getClasses
    where	1=1
    and getbreakouts.time_start between #dtThisMonth# and #dtEndMonth#
    and	getBreakouts.CLASS_ID = getClasses.id	
</cfquery>

Open in new window

0
 

Accepted Solution

by:
wkolasa earned 0 total points
ID: 24159035
Got it:  I went with <cfset dtEndMonth = DateAdd("d",-1,dtNextMonth)>

I'm posting full solution below:
<form action="index.cfm?pageid=#request.pageid#" method="post" name="form1">
<tr>
      <td>Select Month</td>
	  <td>
	  	<cfparam name="CurntDate" default="#now()#">
		<cfparam name="YearSet" default="#year(now())#">
		
	    <cfset monthSet = ArrayNew(1)>
        <cfset monthSet[1] = "January">
        <cfset monthSet[2] = "February">
        <cfset monthSet[3] = "March">
        <cfset monthSet[4] = "April">
        <cfset monthSet[5] = "May">
        <cfset monthSet[6] = "June">
        <cfset monthSet[7] = "July">
        <cfset monthSet[8] = "August">
        <cfset monthSet[9] = "September">
        <cfset monthSet[10] = "October">
        <cfset monthSet[11] = "November">
        <cfset monthSet[12] = "December">
        <select name="month">
          <option value="" selected>
          <cfloop index="i" from="1" to="12">
		  
		  <cfif i LT 10>
		  <cfset ldate = DateFormat(CurntDate,"2009/0#i#/dd")>
		  <cfelse>
		  <cfset ldate = DateFormat(CurntDate,"2009/#i#/dd")>
		  </cfif>
		  
            <option value="#ldate#">#monthSet[i]# #YearSet# - value = #ldate#</option>
          </cfloop>
        </select>
		(Leave Blank for All)</td>
    </tr>
<tr>
<td></td><td><input type="Submit" name="view" value="View" class="btn" >
 
</td>
</tr>
<input type="Hidden" name="sendinfo" value="1">
</form>
 
<cfif isDefined('form.month')>
	<cfset dtThisMonth = CreateDate(year(#form.month#),month(#form.month#),1) >
    <cfset dtNextMonth = DateAdd("m",1,dtThisMonth) >
    <cfset dtEndMonth = DateAdd("d",-1,dtNextMonth)>
    
</cfif>
 
<cfquery dbtype="query" name="getBreakouts">
    select 	getBreakouts.*,
            getClasses.class_pts
    from 	getBreakouts,
            getClasses
    where	1=1
    and getbreakouts.time_start between #dtThisMonth# and #dtEndMonth#
    and	getBreakouts.CLASS_ID = getClasses.id	
</cfquery>

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question