extract data between certain date.

[Samurai_Sjakkie]

Hi all. I have a data base with purchases. Now i am trying to write a report
page to allow me to see what has been bougth between certain dates. I have 3
select html forms. one for year #form.startyear# one for the month
#form.startmonth# and one for day #form.startday# i have then 3 more which are
#form.endyear#, #form.endmonth# and #form.endday#. how could i use coldfusion
for this report.

maybe something like... this is all new to me so any help would be
appreciated....




<cfset StartDate = #form.startyear#, #form.startmonth# and #form.startday#>
<cfset EndDate = #form.endyear#, #form.endmonth# and #form.endday#>

<cfquery name="test456" datasource="test">
SELECT *
From purchases
WHERE ((purchases.testDate BETWEEN #CreateODBCDate(StartDate)# AND
#CreateODBCDate(EndDate)#))
ORDER BY Name
</cfquery>

[Dan Bracuk]

First of all, using 3 selects in your form enables users to send you values
like Feb 31 which is not a valid date. You'll have to validate your the
submission before you do anything.

Assuming you have done that, things would be simpler to envision if you have
leading zero's in your month and day fields. If you didn't handle that in your
form, use numberformat() to do do it on your processing page. Then you can do
this

<cfset startdate = createodbcdate(form.startyear & "-" & form.startmonth & "-"
& form.startday)>

same thing for enddate.

in your query you don't need all those brackets.

where purchases.testdate between StartDate and EndDate

[Samurai_Sjakkie]

sorry its all a bit confusing.
so it is

<cfset startdate = createodbcdate(form.startyear & "-" & form.startmonth & "-"
& form.startday)>
<cfset enddate = createodbcdate(form.endyear & "-" & form.endmonth & "-" &
form.endday)>

<cfquery name="test456" datasource="test">
SELECT *
From purchases
where #purchases.testdate# between #StartDate# and #EndDate#
ORDER BY Name
</cfquery>

what if i have an "all" option within the #form.startyear# and#from.endyear#
which has a value of "0" could i run something to check for this first and
report all the purchases and if it hasn't been selected that it does the above
query.

My apologies, i suffer from learning difficulties.