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:1892
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
