Here's some data to go along with the question below:
+----------+-------------+-------+-----------+----------+---------------------+---------------------+---------+-------------+------------+
| RecordID | AggregateID | Count | dataMonth |
dataYear | Created_DT | Updated_DT |
LogonID | InputMethod | dataDate |
+----------+-------------+-------+-----------+----------+---------------------+---------------------+---------+-------------+------------+
| 120001 | BRN01011201 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:09:09 |
davmoto | 2 | 2006-04-01 |
| 120002 | BRN01010101 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:09:09 |
davmoto | 2 | 2006-04-01 |
| 120003 | BRN01011201 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:09:19 |
davmoto | 2 | 2006-04-01 |
| 120004 | BRN01010101 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:09:19 |
davmoto | 2 | 2006-04-01 |
| 120005 | BRN01011201 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:11:26 |
davmoto | 2 | 2006-04-01 |
| 120006 | BRN01010101 | 1 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:11:26 |
davmoto | 2 | 2006-04-01 |
| 120007 | LAN01011202 | 2 | 4 |
2006 | 2006/4/3 14:00:00 P | 2006-04-03 16:15:53 |
davmoto | 2 | 2006-04-01 |
| 120008 | LAN01011201 | 4 | 4 |
2006 | 2006/4/3 14:00:00 P | 2006-04-03 16:15:53 |
davmoto | 2 | 2006-04-01 |
| 120009 | LAN01011202 | 2 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:16:04 |
davmoto | 2 | 2006-04-01 |
| 120010 | LAN01011201 | 2 | 4 |
2006 | 2006/4/3 15:00:00 P | 2006-04-03 16:16:04 |
davmoto |
2 | 2006-04-01 |
| 61646 | GRN01010101 | 10 | 6 |
2005 | 2005/07/22 10:15:33 | 2005-07-22 11:37:26 |
davmoto | 1 | 2005-06-01 |
| 61647 | GRN01011201 | 10 | 6 |
2005 | 2005/07/22 11:37:26 | 2005-07-22 11:37:26 |
davmoto | 1 | 2005-06-01 |
| 61704 | GRN01011201 | 11 | 6 |
2005 | 2005/07/22 14:30:38 | 2005-07-22 14:30:38 |
davmoto | 1 | 2005-06-01 |
| 61705 | GRN01010101 | 11 | 6 |
2005 | 2005/07/22 14:30:38 | 2005-07-22 14:30:38 |
davmoto | 1 | 2005-06-01 |
| 61706 | GRN01011201 | 11 | 6 |
2005 | 2005/07/22 14:32:16 | 2005-07-22 14:32:16 |
davmoto | 1 | 2005-06-01 |
| 61707 | GRN01010101 | 11 | 6 |
2005 | 2005/07/22 14:32:16 | 2005-07-22 14:32:16 |
davmoto | 1 | 2005-06-01 |
| 61710 | GRN01010101 | 12 | 6 |
2005 | 2005/07/22 14:34:35 | 2005-07-22 14:34:35 |
davmoto | 1 | 2005-06-01 |
| 61711 | GRN01011201 | 12 | 6 |
2005 | 2005/07/22 14:34:35 | 2005-07-22 14:34:35 |
davmoto | 1 | 2005-06-01 |
| 62138 | GRN01080101 | 120 | 6 |
2005 | 2005/07/25 15:11:37 | 2005-07-25 15:11:37 |
davmoto | 1 | 2005-06-01 |
| 62139 | GRN01011201 | 119 | 6 |
2005 | 2005/07/25 15:11:37 | 2005-07-25 15:11:37 |
davmoto | 1 | 2005-06-01 |
--- R Fields <[EMAIL PROTECTED]> wrote:
> 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:1893
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
