Rick,
I think you might be able to simplify this further, and take ColdFusion out of
the query altogether. Bear in mind I've never used MySQL, but according to the
MySQL docs something like this should work:
select totalNew
from 04a_dailyNumberNewHMLSProperties
where datediff(curdate(),dateOfTotal) = 1
The DateDiff(expr1,expr2) function "returns expr1 â expr2 expressed as a
value in days from one date to the other. expr1 and expr2 are date or
date-and-time expressions. Only the date parts of the values are used in the
calculation."
HTH,
Carl
>Something is still not right...
>
>Given this data set:
>
>Record 1: CountID 1, totalNew 927, dateOfTotal 2010-11-04 14:14:02
>Record 2: CountID 2, totalNew 901, dateOfTotal 2010-11-05 15:04:20
>
>And these queries:
>
><cfquery name = "getYesterdayNewHMLSProperties" datasource="myDatasource">
>
> select totalNew
> from 04a_dailyNumberNewHMLSProperties
> where day(date_add(dateOfTotal, interval -1 day)) =
>#dateFormat(dateAdd('d', -1, now()), 'd')#
>
></cfquery>
>
><cfquery name = "getTodayNewHMLSProperties" datasource="myDatasource">
>
> select totalNew
> from 04a_dailyNumberNewHMLSProperties
> where day(dateOfTotal) = #dateFormat(now(), 'd')#
>
></cfquery>
>
>I'm getting "totalNew" in both queries as "901".
>
>Seems to me that the first query "Yesterday" should return 927.
>Seems to me that the second query "Today" should return 901.
>
>But when I output getYesterdayNewHMLSProperties.totalNew, I get 901, instead
>of 927.
>And when I output getTodayNewHMLSProperties.totalNew, I get 901, which is
>correct.
>
>I've verified that day(dateOfTotal) returns "5" and #dateFormat(now(), 'd')#
>returns 5. Also, day(date_add(dateOfTotal, inverval -1 day)) returns "4" and
>#dateFormat(dateAdd('d', -1, now()), 'd')# returns "4". So the queries are
>operating on the correct "day of the month".
>
>The variables aren't overwriting each other somehow, are they?
>
>This is just one of those days where I wonder If I have any future in this
>business.
>
>Rick
>
>
>
>Thanks, everyone, for the feedback.
>
>I *finally* got this stupid (that's the headache talking)
>query working. Seems like working with comparing dates
>in queries has always been a chore. Mostly because I do it
>rarely.
>
>And, everyone was right, I did have MySQL working on the
>part that CF should have been processing.
>
>I did mention in an earlier post that it was MySQL 5.
>
>Anyway, here's what finally worked:
>
>select totalNew
>from 04a_dailyNumberNewHMLSProperties
>where day(date_add(dateOfTotal, interval -1 day)) =
>#dateFormat(dateAdd('d', -1, now()), 'd')#
>
>Now I can rest in peace. (or pieces if my headache
>does finally split my head)
>
>Have a good evening everyone!
>
>Rick
>
>Apologies Rick....I now see that dateOfTotal is the new column name.
>
>You have it backwards....you are using the DB function (date_add()) on
>now() and should be using dateAdd() (CF function)....likewise...where
>you are using dateAdd() you should be using date_add().
>
>Cheers
>
>
>Bryan Stevenson B.Comm.
>VP & Director of E-Commerce Development
>Electric Edge Systems Group Inc.
>phone: 250.480.0642
>fax: 250.480.1264
>cell: 250.920.8830
>e-mail: br...@electricedgesystems.com
>web: www.electricedgesystems.com
>
>Notice:
>This message, including any attachments, is confidential and may contain
>information that is privileged or exempt from disclosure. It is intended
>only for the person to whom it is addressed unless expressly authorized
>otherwise by the sender. If you are not an authorized recipient, please
>notify the sender immediately and permanently destroy all copies of this
>message and attachments.
>Please consider the environment before printing this e-mail
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338881
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
