Date issue in coldfusion

Question

I have a report which has the date selection as show below. When the report is first loaded it will show the data for the current month ie in this case from {ts '2014-06-01 00:00:00'} to {ts '2014-07-01 00:00:00'};range in all the queries on the page. When I select a date from the dropdown and submit the page, the queries still take the start and end date as 6/1 to 7/1 instead of the selected month. I tried debugging it but still not able to get it to work. Any suggestions here?

I have updated My form is like below. I want the first radio button to be selected on the first page load, I am just making the other selection empty on click of the radio button. Is there a way to save what is in the selection and just display results for the criteria selected?please suggest

   <script>
 function makeChoice() {
   var obj1 = document.getElementById('month_dt')
   var obj2 = document.getElementById('start_date')
   var obj3 = document.getElementById('end_date')

        if (document.getElementById('but1').checked) {                  
              obj2.value = '';  
              obj3.value = '';      
        }
        else if (document.getElementById('but2').checked) {
              obj1.value = '';          

        }           
    }  

   </script>
 <form name="month_year" method="get">
<div>
    <table>
    <tr>

        <th align="right">
        <input type="radio" name="datesel" value="1" id="but1" onClick="return makeChoice();"/>

        Month/Year:</th>
        <td align="left">
            <select name="month_dt" id="month_dt">
                <option value="">---Select one---</option>
                <cfloop from="0" to="#diff_month_cnt#" index="ii">
                    <cfset temp_dt = dateAdd("m", -1 * ii, today) />
                    <cfset temp_dt = createDate(year(temp_dt), month(temp_dt), 1) />
                    <cfset isselected = "" />
                    <cfif temp_dt EQ the_month_dt>
                        <cfset isselected = "selected" />
                    </cfif>
                    <option value="#dateFormat(temp_dt, 'mm/dd/yyyy')#" #isselected#>#dateFormat(temp_dt, "mmmm yyyy")#</option>
                </cfloop>
             </select> 

        </td>
    </tr>

    <tr><td colspan="2" align="center"><em>- or -</em></td></tr>
    <tr>

        <th align="right"> <input type="radio" name="datesel" value="2" id="but2" onClick="return makeChoice();"/>Start Date:</th>
        <td>
            <input id="start_date" type="text" name="start_date" <cfif isdate(url.start_Date)>value="#dateFormat(url.start_date, 'mm/dd/yyyy')#"</cfif> size="10" maxlength="10" autocomplete="off" />

        </td>
    </tr>
    <tr>
        <th align="right">End Date:</th>
        <td>
            <input id="end_date" type="text" name="end_date" <cfif isdate(url.end_Date)>value="#dateFormat(url.end_date, 'mm/dd/yyyy')#"</cfif> size="10" maxlength="10" autocomplete="off" />
        </td>
    </tr>

<input type="submit" value=" OK " />



 <cfif isDate(url.month_dt) and url.datesel eq 1>
    <cfset begin_dt = createDate(year(month_dt), month(month_dt), 1) /> 
    <cfset end_dt = dateAdd("m", 1, begin_dt) />    
<cfelseif isDate(url.start_date) AND isDate(url.end_date) and url.datesel eq 2 >
    <cfset begin_dt = url.start_date />
    <cfset end_dt = url.end_date />
</cfif>

<cfset the_month_dt = createDate(year(begin_dt), month(begin_dt), 1) />

In the queries like

 1)   WHERE q.quizdate >= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#the_month_dt#" />
       AND q.quizdate < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateAdd('m', 1, the_month_dt)#" />

 2)   WHERE r.create_dt >= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#begin_dt#" />
           AND r.create_dt < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#end_dt#" />

Answer 1

You need to change the following code:

<cfif isDate(url.month_dt)>
    <cfset begin_dt = createDate(year(month_dt), month(month_dt), 1) /> 
</cfif>    
<cfif isDate(url.start_date)>
    <cfset begin_dt = url.start_date />
</cfif>    
<cfset end_dt = dateAdd("m", 1, begin_dt) />    
<cfif isDate(url.end_date)>
    <cfset end_dt = url.end_date />
</cfif>

to be:

<cfif isDate(url.month_dt)>
    <cfset begin_dt = createDate(year(month_dt), month(month_dt), 1) /> 
    <cfset end_dt = dateAdd("m", 1, begin_dt) />    
<cfelseif isDate(url.start_date) AND isDate(url.end_date)>
    <cfset begin_dt = url.start_date />
    <cfset end_dt = url.end_date />
</cfif>

Answer 2

Use DaysInMonth to look for the last date in month. As more info is given in the question, my answer is modified a bit. You need to disable and clear the values in Start/End form fields when a user chooses to select by month and vice versa. Otherwise, the users might get confused. Place the code before the form to get the start/end dates selected by a user. The final start/end dates are begin_dt and end_dt

<cfset thisMonth = createDate(year(now()), month(now()), 1) />

<cfparam name="url.month_dt" default="" />
<cfparam name="url.start_date" default="" />
<cfparam name="url.end_date" default="" />

<cfparam name="bRadioMonth" default="" />
<cfparam name="bRadioStartEnd" default="" />

<cfif len(url.month_dt) AND isDate(url.month_dt)>
  <cfset begin_dt = createDate(year(url.month_dt), month(url.month_dt), 1) />
  <cfset end_dt = createDate(year(begin_dt), month(begin_dt), DaysInMonth(begin_dt)) />
  <!--- Disable form fields start_date and end_date --->
  <cfset bFieldMonth = "">
  <cfset bFieldStartEnd = "disabled">
  <cfset bRadioMonth = "checked">

<cfelseif len(url.start_date) AND len(url.end_date)
      AND isDate(url.start_date) AND isDate(url.end_date)>

  <cfset begin_dt = createDate(year(url.start_date), month(url.start_date), day(url.start_date)) />
  <cfset end_dt = createDate(year(url.end_date), month(url.end_date), day(url.end_date)) />
  <!--- Disable form field month_dt --->
  <cfset bFieldMonth = "disabled">
  <cfset bFieldStartEnd = "">
  <cfset bRadioStartEnd = "checked">

<cfelse>

  <cfset begin_dt = thisMonth />
  <cfset end_dt = createDate(year(begin_dt), month(begin_dt), DaysInMonth(begin_dt)) />
  <!--- Default to disable form fields start_date and end_date --->
  <cfset bFieldMonth = "">
  <cfset bFieldStartEnd = "disabled">
  <cfset bRadioMonth = "checked">

</cfif>

javascript:

<script>
function makeChoice() {
  var fieldMonth = document.getElementById('month_dt');
  var fieldStart = document.getElementById('start_date');
  var fieldEnd = document.getElementById('end_date');

  if (document.getElementById('but1').checked) {
    fieldMonth.disabled = false;
    fieldStart.disabled = true;
    fieldEnd.disabled = true;
    fieldStart.value = '';
    fieldEnd.value = '';
  }
  else if (document.getElementById('but2').checked) {
    fieldMonth.options[0].selected = true;
    fieldMonth.disabled = true;
    fieldStart.disabled = false;
    fieldEnd.disabled = false;
  }
}
</script>

If form is submitted, return selected start/end dates and run query:

<cfoutput>
Selected Start Date: #dateFormat(begin_dt, 'mm/dd/yyyy')# End Date: #dateFormat(end_dt, 'mm/dd/yyyy')#
</cfoutput>

<cfif isdefined("form.submit")>
<!---
Run Query.
 1)   WHERE q.quizdate >= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#begin_dt#" />
       AND q.quizdate < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DATEADD('d',1,end_dt)#" />

 2)   WHERE r.create_dt >= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#begin_dt#" />
           AND r.create_dt < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#DATEADD('d',1,end_dt)#" />
--->
</cfif>