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>

About This Tutorial
Author: Jim Summer
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5
Total Views: 110,120
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 5
Discuss This Tutorial
  • Had so much trouble with date comparisons in SQL til I found this sample code.... very cool stuff: TRY THIS

  • I finally got this to work, I just had to place cfoutput along with the code in the value line like this. "#DateFormat(Now(), 'mm/dd/yyyy')#" without the output line I kept getting the variable passed like the first person in the post.

  • I was wondering if anyone would know how to get the information from a DB with one data column then get the date range and display the data by each individual month example: start_date = 6/1/2005 end_date = 7/31/2005(passed form values). so Basiclly requery the database for 6/1/2005-6/30/2005 and also for 7/1/2005 - 7/31/2005 then display the data on the page. by month. How to go about doing this is the Question. Here is what I got so far. Select * FROM dbo.dailyClaims WHERE Process_Date >= '#st_date#' AND Process_Date <= '#en_date#' ORDER BY Process_Date ASC

  • If you use this on the file it works really well:

    DateFormat Example

    Today's date is #todayDate#.

    Using DateFormat, we can display that date in different ways:

    • #DateFormat(todayDate)#
    • #DateFormat(todayDate, "mmm-dd-yyyy")#
    • #DateFormat(todayDate, "mmmm d, yyyy")#
    • #DateFormat(todayDate, "mm/dd/yyyy")#
    • #DateFormat(todayDate, "d-mmm-yyyy")#
    • #DateFormat(todayDate, "YYYYmmdd")#
    • #DateFormat(todayDate, "ddd, mmmm dd, yyyy")#
    • #DateFormat(todayDate, "short")#
    • #DateFormat(todayDate, "medium")#
    • #DateFormat(todayDate, "long")#
    • #DateFormat(todayDate, "full")#
    Then you can set the format to whatever you like when it goes into the SQL table

  • This inserts but not the data/ The variable itself gets passed to the database???

Advertisement


Website Designed and Developed by Pablo Varando.