EasyCFM.COM ColdFusion Tutorial: Database Dates (between ranges)

Server Time:
Tuesday May 13 2008 05:42 PM

Your Time:

HostMySite.Com is sponsoring this tutorial, please visit their site today!

This tutorial is sponsored by HostMySite.Com - ColdFusion Hosting

Database Dates (between ranges)
by: Jim Summer

[Download in PDF Format] [Download in FlashPaper Format]

1) The form that inserts the date into the database

<input type="text" name="uName">

<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>
   
   <cfif #testresults.recordcount# is 0>
   <tr>
      <td colspan="2" align="center">No records found in that date range</td>
   </tr>
</cfif>

<cfoutput query="testresults">
   <tr>
      <td>#testresults.uName#</td>
      <td>#DateFormat(testresults.testDate, 'mm/dd/yyyy')#</td>
   </tr>
</cfoutput>
</table>

Date added: Mon. December 9, 2002
Posted by: Jim Summer | Views: 17330 | Tested Platforms: CF5 | Difficulty: Intermediate
Categories Listed: Dates/Time SQL

HostMySite.Com is sponsoring this tutorial, please visit their site today!

This author's other tutorials:

Search Engine Bot Notifier
This code detects the most common user agents (web browsers) and notifies you via email if it is not a recognized user agent as defined in the code. Usually this will be a bot of some sort. Extrememly useful for tracking how often Google, Yahoo, etc visits your site. It will email you the bot and a reverse IP lookup url with the IP appended so you can verify if it is a "good bot" or a "bad (spam) bot" (then you can block that IP or stop processing of the page). Use this in your home page or as an include file throughout your site. Nothing fancy it should work on MX also although I have not tested it there. - Date added: Wed. April 14, 2004
Replacing "enter" key with "<br>" tag
This little piece of code will transform those pesky "enter" keys in a textarea into "<br>" tags so your users input is printed out properly in your html page. - Date added: Fri. December 13, 2002
Navigation as an include file
Create an include file (custom tag) for your navigation to help make maintenance easier! This include file lights up the button depending on where the user is at in your website, and is XHTML 1.1 validated! If you need to edit your navigation, just do it in 1 place, the include file! Javascript included :) - Date added: Thu. December 12, 2002
Aliasing Your SQL Statements
Many developers I have talked to are not aware of the ability to create an "alias" in an SQL statement, concatenate, and even add strings into your SQL statements. A little work up front in the SQL can cut out a lot of tedious coding in the <cfoutput> tag. - Date added: Tue. December 10, 2002
Recordset Paging in Cold Fusion
This will pull a predefined number of records from a database, allow the user to change the number of records to be shown, and write the "NEXT" or "BACK" (or both) buttons at the bottom of the page. Thus allowing the user to "surf" through the database. See it in action at http://freecfm.com/t/tentonhead/ and click on the "CTCS" link when you get there. The HTML for this page has changed a little since I first did this (so it validates as XHTML1.1) but the CFML remains as it is in this snippet. - Date added: Mon. December 9, 2002

Comments on this tutorial
Read previous comments on this particular tutorial
no date This inserts but not the data/ The variable itself gets passed to the database??? Posted by: ANgel Posted on: 01/03/2005 10:39 AM
Just use cfset If you use this on the file it works really well: <cfset todayDate = Now()> <body> <h3>DateFormat Example</h3> <p>Today's date is <cfoutput>#todayDate#</cfoutput>. <p>Using DateFormat, we can display that date in different ways: <cfoutput> <ul> <li>#DateFormat(todayDate)# <li>#DateFormat(todayDate, "mmm-dd-yyyy")# <li>#DateFormat(todayDate, "mmmm d, yyyy")# <li>#DateFormat(todayDate, "mm/dd/yyyy")# <li>#DateFormat(todayDate, "d-mmm-yyyy")# <li>#DateFormat(todayDate, "YYYYmmdd")# <li>#DateFormat(todayDate, "ddd, mmmm dd, yyyy")# <li>#DateFormat(todayDate, "short")# <li>#DateFormat(todayDate, "medium")# <li>#DateFormat(todayDate, "long")# <li>#DateFormat(todayDate, "full")# </ul> </cfoutput> Then you can set the format to whatever you like when it goes into the SQL table Posted by: Fran Posted on: 01/03/2005 10:55 AM
Date Range 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. <cfparam name="URL.st_date" default=""> <cfparam name="URL.en_date" default=""> <cfif (URL.st_date) NEQ "" AND (URL.en_date) NEQ ""> <cfif DateCompare("#url.st_date#","#url.en_date#") NEQ 0> <cfset st_date = #URL.st_date#> <cfset en_date = #URL.en_date#> <!---Start Paid Report Query---> <cfquery name="paid" datasource="FRG"> Select * FROM dbo.dailyClaims WHERE Process_Date >= '#st_date#' AND Process_Date <= '#en_date#' ORDER BY Process_Date ASC </cfquery> </cfif> </cfif> Posted by: rey Posted on: 08/31/2005 01:37 PM
place cfoutput I finally got this to work, I just had to place cfoutput along with the code in the value line like this. <cfoutput>"#DateFormat(Now(), 'mm/dd/yyyy')#"</cfoutput> without the output line I kept getting the variable passed like the first person in the post. Posted by: Eddie Posted on: 04/21/2007 06:38 PM
try this too Had so much trouble with date comparisons in SQL til I found this sample code.... very cool stuff: <a href="http://www.thisiscode.com/sql/comparedates.php" target="_blank">TRY THIS</a> Posted by: Sean Bengry Posted on: 08/07/2007 08:56 PM

You are 1 of 815 active sessions! | Privacy | Company
Copyright © 2002 EasyCFM.Com, LLC. (Easy ColdFusion Tutorials) All Rights Reserved
All other trademarks and copyrights are the property of their respective holders.

ColdFusion Hosting

ADD TO:

Blink

Del.icio.us

Digg

Furl

Google

Simpy

Spurl

Y! MyWeb