Had so much trouble with date comparisons in SQL til I found this sample code.... very cool stuff: TRY THIS
By: Jim Summer
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
Total Views: 51,349
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 5
Skill Level: Intermediate
Platforms Tested: CF5Total Views: 51,349
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 5
Discuss This Tutorial
-
-
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")#
-
This inserts but not the data/ The variable itself gets passed to the database???
Advertisement
