A couple things:
First, it looks like you're not really using cfparam properly. What you want
to do is use it to ensure that a variable is undefined, not to set it. What
you're doing is functionally no different than using <cfset Start1 =
FORM.Start1 /> You should be doing <cfparam name="FORM.start1"
default="myDefaultValue" /> so you are sure that FORM.start1 contains
something. The way you've written it if for some reason FORM.start1 doesn't
exist the page goes kablammo.
Secondly, to your original question. If you've got a string containing a date
in mm/dd/yyyy format and you want to use it in a SQL query you should use
<cfqueryparam cfsqltype="cf_sql_date" value="#Start1#" />
You'll probably want to try some validation on the input to be sure it conforms
with a supported date string.
>Hello,
>
>My CF application has code to run reports on the data
>collected. I'm having a tough time with how CF views
>a date and how MySQL views dates.
>
>My report form asks the user to enter a range of dates
>in this format: mm/dd/yyyy "to" mm/dd/yyyy
>
>Those dates are Start1 and End1.
>
>My report output page has to go through a number of
>MySQL queries to obtain the corret data that is w/in
>the date range.
>
>Here's some of the code that occurs on this page:
><cfparam name="Start1" default = "#FORM.Start1#">
><cfparam name="End1" default = "#FORM.End1#">
><cfset Start1b = DateFormat(DateAdd('d',0,#Start1#),
>"yyyy/mm/dd")>
><cfset End1b = DateFormat(DateAdd('d',0,#End1#),
>"yyyy/mm/dd")>
>
>I've set a Start1b and End1b variable so I can get the
>date into yyyy/mm/dd format for MySQL to process
>properly.
>
>My question is, how can I properly convert a
>mm/dd/yyyy date into yyyy/mm/dd date? DateFormat
>seems to convert the date into a string, causing
>problems in my query.
>
>Here's part of my query that is having the problem:
>
>WHERE SUBSTRING(RS.AggregateID, 8, 2) NOT IN
>('00','07','08','09')
> AND
> (
> (
> Date_Format(CREATED_DT, '%Y/%m/%d')
> BETWEEN '#Start1b#' AND '#End1b#'
> AND InputMethod = 2
> ) OR
> (
> CAST(CONCAT_WS('-', dataYear, dataMonth, '01') AS
>DATE)
> BETWEEN #CreateODBCDate(Start1b)# AND
>#CreateODBCDate(End1b)#
> AND InputMethod = 1
> )
> )
> GROUP BY CASE
> WHEN SUBSTRING(RS.AggregateID, 1, 3) <>'' THEN
>SUBSTRING(RS.AggregateID, 1, 3)
> END
> WITH ROLLUP
>
>When I do a <cfdump> of this query, it runs fine, but
>there is something with how it is handling the dates
>that's causing the rest of the CF page to loop the
>query.
>
>Anyway, what is wrong with this query? What is wrong
>with my variables?
>
>Thanks in advance for your assistance.
>Ronnie
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1894
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
