• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Database Dates (between ranges)

1) The form that inserts the date into the database

<input type="text" name="uName">
<!-- HERE WE FORMAT THE DATE PROPERLY TO INSERT INTO DATABASE USING A HIDDEN FIELD-->
<input type="hidden" name="testDate" value="#DateFormat(now(), 'mm/dd/yyyy')#">
<input type="submit" value="Submit Your Answers">
---------------------------------------
2) The SQL that inserts the data:
<CFINSERT DATASOURCE="mywebsite" TABLENAME="tblTestResults" FORMFIELDS="uName, testDate">
---------------------------------------
3) The form to search date ranges:
The dates are prefilled with today's date to prompt the user for the correct date syntax. When the user clicks or tabs into the text box the dates dissappear. SYNTAX EX: 12/07/1941

<p>Start Date: <input type="text" name="startDate" value="#DateFormat(Now(), 'mm/dd/yyyy')#" onfocus="this.value=''" style="background-color:##f2f2f2;"><br>
End Date: <input type="text" name="endDate" value="#DateFormat(Now(), 'mm/dd/yyyy')#" onfocus="this.value=''" style="background-color:##f2f2f2;">
---------------------------------------
4) The SQL that queries the dates between the prescribed ranges in the form:

<cfquery name="testresults" datasource="mywebsite">
    SELECT * 
    From tblTestResults
    WHERE ((tblTestResults.testDate BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)) 
    ORDER BY uName
</cfquery>

---------------------------------------
5) And displaying the results of the date range query:

<table cellpadding="1" cellspacing="0" width="100%" border="1" bordercolor="silver">
   <tr>
     <th>Member Name</th>
     <th>Completed</th>
   </tr>
   <!-- IF THERE ARE NO RESULTS -->
   <cfif #testresults.recordcount# is 0>
   <tr>
      <td colspan="2" align="center">No records found in that date range</td>
   </tr>
</cfif>
<!-- IF THERE ARE RESULTS THEN DISPLAY THEM -->
  <cfoutput query="testresults">
   <tr>
      <td>#testresults.uName#</td>
      <td>#DateFormat(testresults.testDate, 'mm/dd/yyyy')#</td>
   </tr>
  </cfoutput>
</table>