I don't think that is the problem. I do not need to validate their information
as it is a selection in the form... Here is part of that code:
<td width="50%" valign="top">
<div align="right">Select Quarter: <div>
</td>
<td>
<div align="left">
<select name="Qrtr" size="4">
<option value="Q1">Quarter 1: January 1 - March 31</option>
<option value="Q2">Quarter 2: April 1 - June 30</option>
<option value="Q3">Quarter 3: July 1 - September 30</option>
<option value="Q4">Quarter 4: October 1 - December 31</option>
</select>
</div>
</td>
</tr>
<tr>
<td>
<div align="right">Enter Year: </div>
</td>
<td>
<div align="left">
<cfinput type="text" name="yr" value="" size="10" maxlength="10"
message="Year Required" validateat="onserver" required="yes">
</div>
</td>
</tr>
Then on the next page...
<cfset yr = #FORM.yr#>
<cfset qrtr = #FORM.Qrtr#>
<cfif qrtr IS "Q1">
<cfset dateCreateMin = CreateDate(FORM.yr, 1, 1)>
<cfset dateCreateMax = CreateDate(FORM.yr, 3, 31)>
<cfset dateMin = DateFormat(dateCreateMin, 'mm/dd/yyyy')>
<cfset dateMax = DateFormat(dateCreateMax, 'mm/dd/yyyy')>
<CFSET dateMinNew = #CREATEODBCDATETIME("01/01/2007 00:00:00")#>
<CFSET dateMaxNew = #CREATEODBCDATETIME("03/31/2007 23:59:59")#>
<cfelseif qrtr IS "Q2">
(repeats for Q2, Q3, and Q4 with respective dates)
(For testing)
<cfoutput>
dateMin: #dateMin#<br />
dateMax: #dateMax#<br />
dateMinNew: #dateMinNew#<br />
dateMaxNew: #dateMaxNew#<br />
</cfoutput>
(Output)
dateMin: 01/01/2007
dateMax: 03/31/2007
dateMinNew: {ts '2007-01-01 00:00:00'}
dateMaxNew: {ts '2007-03-31 23:59:59'}
So you can see that the information is being validated by myself (except for
the year and I am fine with that). I am using CREATEODBCDATETIME to get the
date into the correct format. Then when I perform my query with the following
code:
<cfquery name="qGetCertificates" datasource="xxxx">
SELECT eMail, issueDate, testName, namePDF
FROM certificates
WHERE issueDate between #dateMinNew# and #dateMaxNew#
</cfquery>
<table border="0" width="900">
<tr>
<th>Quarterly Report</th>
</tr>
<tr>
<td>Issue Date</td>
</tr>
<cfoutput query="qGetCertificates">
<tr>
<td>#qGetCertificates.issueDate#</td>
</tr>
</cfoutput>
</table>
I get every record in the DB even if it is outside the dates requested. If I
did not understand your reference on
"http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functi49.htm#wp1102341";,
please forgive me, however I did not see anything about querying a date on that
page and I think that is where my problem is - the query is misinterpreting my
dates somehow.
Now that I think about it, I think I have found the problem. Could it be
because in the DB, my issueDate column is told to be formatted as "Text" and
not "Date/Time"?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2746
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
