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
-----Original Message-----
From: Rick Faircloth [mailto:[email protected]]
Sent: Friday, November 05, 2010 5:45 PM
To: cf-talk
Subject: RE: I think I'm just making this way too hard...
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
-----Original Message-----
From: Bryan Stevenson [mailto:[email protected]]
Sent: Friday, November 05, 2010 4:50 PM
To: cf-talk
Subject: RE: I think I'm just making this way too hard...
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: [email protected]
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:338875
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
