There is always more than one way to do something!
I did not want to change the database field form text to date/time because I am
afraid for other possible problems that might occur from chaning something that
is alreay working properly.
Solution (Theory):
Take the date from the database and reformat it to mm/dd/yyyy.
Then re-arrange the date to the following yyyymmdd.
Then when I use the Greater Than and Less Than fields in my WHERE clause in the
query, it will work - and it does!
Solution (Selective parts of the Code):
<cfset yr = #FORM.yr#>
<cfset qrtr = #FORM.Qrtr#>
<cfif qrtr IS "Q1">
<cfset dateMinimum = FORM.yr & "0101">
<cfset dateMaximum = FORM.yr & "0331">
<cfelseif qtr IS "Q2">
<!--- respective code for Q2 - Q4, note that the year is first, day is
second, and month is last all part of an 8 digit number --->
</cfif>
<cfquery name="qGetCertificates" datasource="xxxxxx">
SELECT eMail, issueDate, testName, namePDF
FROM certificates
WHERE namePDF<>NULL
</cfquery>
<cfoutput query="qGetCertificates">
<cfset issueDateFormatted = DATEFORMAT(#qGetCertificates.issueDate#,
'mm/dd/yyyy')>
<!--- Makes sure that the there will be zeros as placeholders --->
<cfset myMonth = Mid(issueDateFormatted, 1, 2)>
<!--- Selects the first 2 digits as the month --->
<cfset myDay = Mid(issueDateFormatted, 4, 2)>
<!--- selects the 4 and 5 digits as the day --->
<cfset myYear = Mid(issueDateFormatted, 7, 4)>
<!--- selects the last 4 digits as the year --->
<cfset certCreationReformatted = #myYear# & #myMonth# & #myDay#>
<!--- places the previous variables into an 8 digit number with year first,
month second, and day last--->
<cfif certCreationReformatted GE dateMinimum AND certCreationReformatted LE
dateMaximum>
<!--- Then enter data into the table --->
I realize that this probably is not the most efficient way to do this but it
serves my purpose. Thanks everyone who helped! Let me know if you need more
code / clarification.
:)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2749
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
