variables you input are CF date objects by using createDate or
ParseDate etc.
Once you have the month and year you require I would construct the
first of that mnnth and the end of the month.
<cfset start_date =
createdate(datepart('yyyy',form.monthSEL),datepart('m',form.monthSEL),1)>
<cfset end_date = dateAdd('d',-1,dateAdd('m',1,start_date))>
<cfoutput>
form.SELmonth = #dateformat(form.SELmonth,"dd-mmm-yyyy")#<br>
start_date = #dateformat(start_date,"dd-mmm-yyyy")#<br>
end_date = #dateformat(end_date,"dd-mmm-yyyy")#<br>
</cfoutput>
You can then do a select something like
Select * from myDATA
where (#form.dateFIELD# >= <cfqueryparam cfsqltype="CF_SQL_DATE"
value="#start_date#">
or
#form.dateFIELD# <= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#end_date#">
)
Hope that Helps (HTH)
----- Original Message -----
From: Les Mizzell <[EMAIL PROTECTED]>
Date: Fri, 10 Sep 2004 16:46:06 -0400
Subject: Dates in Queries give me fits! Advice please?
To: CF-Talk <[EMAIL PROTECTED]>
I need to select all records (SQL Server) where are particular date
field matches the MONTH selected. My problem comes in where the field
itself is a variable coming from a form.
On the form, there's a dropdown that shows the last 24 months like
(example displays 2 months ago):
<option value="#DateAdd("m",-2,Now())#">
#DateFormat(DateAdd("m",-2,Now()), "mmmm, yyyy")#
</option>
Here's the current SQL (simplified) that ain't working correctly:
Select * from myDATA
where #form.dateFIELD# = #DatePart("m",form.monthSEL)#
#form.dateFIELD# is a dynamic value where you can pick which column to
search. Could be "start_date", "assigned_date", "complete_date"....
So, I need to extract the month value from the dynamic field and compare
it to the value passed from the form.
1. Am I passing the correct value from the form to start with?
2. Using #DatePart("m",form.dateFIELD)# doesn't work correctly - so this
is my main problem. Syntax is throwing me off...
2. How does it know the difference between June 2004 and June 2003?
Pointers?
--
Les Mizzell________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
