Re: I think I'm just making this way too hard...
On 11/5/2010 12:14 PM, Rick Faircloth wrote: What am I doing wrong? (Besides mostly likely making this way harder than it should be... just fog on the brain today) Rick I suspect that datatime is a column name in your database scheme. IF so that would mean you are trying to use a ColdFusion function to parse the value of a database column in the record set from a sql query inside the cfquery... block used to send the SQL statement to the database to ask for a recrord set. Or, maybe, more simply -- you have put the cart before the horse. You need to be using a database function around the database column, which for date functions varies from DBMS to DBMS. ~| 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:338861 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
That's exactly right. On Fri, Nov 5, 2010 at 3:25 PM, Ian Skinner h...@ilsweb.com wrote: On 11/5/2010 12:14 PM, Rick Faircloth wrote: What am I doing wrong? (Besides mostly likely making this way harder than it should be... just fog on the brain today) Rick I suspect that datatime is a column name in your database scheme. IF so that would mean you are trying to use a ColdFusion function to parse the value of a database column in the record set from a sql query inside the cfquery... block used to send the SQL statement to the database to ask for a recrord set. Or, maybe, more simply -- you have put the cart before the horse. You need to be using a database function around the database column, which for date functions varies from DBMS to DBMS. ~| 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:338862 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 12:14 PM, Rick Faircloth wrote: What am I doing wrong? (Besides mostly likely making this way harder than it should be... just fog on the brain today) Rick On 11/5/2010 12:25 PM, Ian Skinner wrote: You need to be using a database function around the database column, which for date functions varies from DBMS to DBMS. P.S. And while there would be nothing wrong with continuing to use a ColdFusion function to create the date time value to be compared to the results of the database function us choose to use around the database column. It might just be simpler to use database function through out, as all DBMS have perfectly fine functions to get the current time from the system. ~| 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:338863 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
plus 1 ;-) DB functions for DB columns...CF functions for values! On Fri, 2010-11-05 at 12:25 -0700, Ian Skinner wrote: On 11/5/2010 12:14 PM, Rick Faircloth wrote: What am I doing wrong? (Besides mostly likely making this way harder than it should be... just fog on the brain today) Rick I suspect that datatime is a column name in your database scheme. IF so that would mean you are trying to use a ColdFusion function to parse the value of a database column in the record set from a sql query inside the cfquery... block used to send the SQL statement to the database to ask for a recrord set. Or, maybe, more simply -- you have put the cart before the horse. You need to be using a database function around the database column, which for date functions varies from DBMS to DBMS. ~| 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:338864 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Well, I switched the DB and CF functions around in the statement,
changed the CF function in the first half of the comparison
to a DB (MySQL 5) function, and changed the column name throwing
the error to dateOfTotal to be certain using datetime as a column
name was not the problem.
But I still get an error, and I can't figure out why. I even renamed
the table, in case using a number at the start of the table name
was problematic.
select totalNew
from a_dailyNumberNewHMLSProperties
where date_add(now(), -1 days) = '#dateAdd('d', -1, dateOfTotal)#'
Variable dateOfTotal is undefined.
How can that column name be undefined? Why is it not being
understood in the SQL?
A!
???
-Original Message-
From: Ian Skinner [mailto:h...@ilsweb.com]
Sent: Friday, November 05, 2010 3:49 PM
To: cf-talk
Subject: Re: I think I'm just making this way too hard...
On 11/5/2010 12:14 PM, Rick Faircloth wrote:
What am I doing wrong? (Besides mostly likely making this
way harder than it should be... just fog on the brain today)
Rick
On 11/5/2010 12:25 PM, Ian Skinner wrote:
You need to be using a database function around the database column,
which for date functions varies from DBMS to DBMS.
P.S. And while there would be nothing wrong with continuing to use a
ColdFusion function to create the date time value to be compared to the
results of the database function us choose to use around the database
column. It might just be simpler to use database function through out,
as all DBMS have perfectly fine functions to get the current time from
the system.
~|
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:338865
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Remove the # signs. You are telling cf to pass dateOfTotal to the server
as a variable value.
-Original Message-
From: Rick Faircloth [mailto:r...@whitestonemedia.com]
Sent: Friday, November 05, 2010 4:21 PM
To: cf-talk
Subject: RE: I think I'm just making this way too hard...
Well, I switched the DB and CF functions around in the statement,
changed the CF function in the first half of the comparison
to a DB (MySQL 5) function, and changed the column name throwing
the error to dateOfTotal to be certain using datetime as a column
name was not the problem.
But I still get an error, and I can't figure out why. I even renamed
the table, in case using a number at the start of the table name
was problematic.
select totalNew
from a_dailyNumberNewHMLSProperties
where date_add(now(), -1 days) = '#dateAdd('d', -1, dateOfTotal)#'
Variable dateOfTotal is undefined.
How can that column name be undefined? Why is it not being
understood in the SQL?
A!
???
-Original Message-
From: Ian Skinner [mailto:h...@ilsweb.com]
Sent: Friday, November 05, 2010 3:49 PM
To: cf-talk
Subject: Re: I think I'm just making this way too hard...
On 11/5/2010 12:14 PM, Rick Faircloth wrote:
What am I doing wrong? (Besides mostly likely making this
way harder than it should be... just fog on the brain today)
Rick
On 11/5/2010 12:25 PM, Ian Skinner wrote:
You need to be using a database function around the database column,
which for date functions varies from DBMS to DBMS.
P.S. And while there would be nothing wrong with continuing to use a
ColdFusion function to create the date time value to be compared to the
results of the database function us choose to use around the database
column. It might just be simpler to use database function through out,
as all DBMS have perfectly fine functions to get the current time from
the system.
~|
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:338866
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 1:20 PM, Rick Faircloth wrote: Variable dateOfTotal is undefined. How can that column name be undefined? Why is it not being understood in the SQL? Because ColdFusion is NOT your database server, it has NO IDEA what column exist or do not exist. It does not see 'dateOfTotal' as a database column. It sees it as a COLDFUSION variable. And it is telling you there is no such variable in your CFML code. All cfquery... does is bundle up a big string and send it to the database. It is the database the is able to parse the string into SQL commands and references to the Database schema, such as Column Names. To do what you want to do you need to include some strings that will represent DATABASE functions inside your cfquery... block, then when the string gets to the database server it will recognize them and give you the results you so desperately desire. Now I can't easily show you how this would work because database date and time functions vary considerably between one Database Management System and another. And you haven't told us what DBMS you are using. ~| 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:338867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 12:14 PM, Rick Faircloth wrote:
select totalNew
from 04a_dailyNumberNewHMLSProperties
where '#dateFormat(datetime, 'mmm d, ')#' =
'#dateFormat(createODBCDateTime(dateDiff('d', '-1', now())), 'mmm d,
')#'
But here is an example of how I would do it on one my Oracle Databases.
SELETCT totalNoe
FROM 04a_dailyNumberNewHMLSPropertise
WHERE to_char(dateTime,'MON D, ') = to_char(CURRENT_DATE, 'MON D, ')
Note: No ColdFusion at all and Oracle has some of the strangest date
time functions around.
~|
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:338868
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Rick you aren't even referencing a DB column anymore???
where date_add(now(), -1 days) = '#dateAdd('d', -1, dateOfTotal)#'
You are comparing the current date -1 day (date_add(now(), -1 days)) to
some date you have -1 day ('#dateAdd('d', -1, dateOfTotal)#')
There is no point to using date_add() as you are not performing that
function against a column in the DB (like your original issue stated).
As far as dateOfTotal not being defined.would have to see the code
where you set it to know why it's not being seen as defined.
Perhaps sticking with your original query and using the date_add()
function for the column.
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:338869
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Apologies RickI now see that dateOfTotal is the new column name. You have it backwardsyou 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:338870 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:br...@electricedgesystems.com]
Sent: Friday, November 05, 2010 4:50 PM
To: cf-talk
Subject: RE: I think I'm just making this way too hard...
Apologies RickI now see that dateOfTotal is the new column name.
You have it backwardsyou 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:338872
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 2:44 PM, Rick Faircloth wrote:
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')#
I'm glad you got something to work! But you may want to reconsider your
choice of function. A quick internet search showed that the MySQL 5
day() function is to: Returns the day of the month for /|date|/, in
the range |1| to |31|
Thus day('2010-11-05') will return 5 as will the associated dateFormat()
function. But since you do not include month or year portion you are
going to return results of every dateOfTotal record that is the 5th day
of the month for every month for every year in your database. I imagine
that is not the desired result.
~|
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:338874
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
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:r...@whitestonemedia.com]
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:br...@electricedgesystems.com]
Sent: Friday, November 05, 2010 4:50 PM
To: cf-talk
Subject: RE: I think I'm just making this way too hard...
Apologies RickI now see that dateOfTotal is the new column name.
You have it backwardsyou 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:338875
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Certainly true, Ian. Once I get the day part working
correctly, I'll add the month and year to it, as well.
I thought I'd go for the most simple approach first, then
make it more complicated.
It's still not returning proper values in the different queries, however.
But at least I'm not getting CF or MySQL errors!
(see the post I just sent for the problem...)
Rick
-Original Message-
From: Ian Skinner [mailto:h...@ilsweb.com]
Sent: Friday, November 05, 2010 5:57 PM
To: cf-talk
Subject: Re: I think I'm just making this way too hard...
On 11/5/2010 2:44 PM, Rick Faircloth wrote:
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')#
I'm glad you got something to work! But you may want to reconsider your
choice of function. A quick internet search showed that the MySQL 5
day() function is to: Returns the day of the month for /|date|/, in
the range |1| to |31|
Thus day('2010-11-05') will return 5 as will the associated dateFormat()
function. But since you do not include month or year portion you are
going to return results of every dateOfTotal record that is the 5th day
of the month for every month for every year in your database. I imagine
that is not the desired result.
~|
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:338876
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Without knowing what the other records in the table may be like I'd have to say there are many records that would contain a date with a day of 4 or 5. IN other words, 4 doesn't just match yesterday, it also matches the 4th day of any month. So if you have older records, that could be skewing the results. Other than that your logic seems soundsbut we don't really have the whole picture... I can say TGIF!! You can take a breather soon Rick!! ;-) 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:338878 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 3:31 PM, Rick Faircloth wrote: Something is still not right... I would suggest you stop looking at date-time values as a collection of sub values, such as Days or Minutes. You need to consider it as a single value that you want to compare to another value. Trying me best to understand the MySQL documentation, it looks like it would be as simple as. WHERE date(date_add(dateOFTotal, interval -1 day))) = date_add(curdate(), interval 01 day) ~| 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:338879 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
On 11/5/2010 3:52 PM, Ian Skinner wrote: WHERE date(date_add(dateOFTotal, interval -1 day))) = date_add(curdate(), interval 01 day) And if I could type and|or proof read: WHERE date(date_add(dateOFTotal, interval -1 day))) = date_add(curdate(), interval -1 day) ~| 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:338880 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: I think I'm just making this way too hard...
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 RickI now see that dateOfTotal is the new column name.
You have it backwardsyou 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
RE: I think I'm just making this way too hard...
Good idea, but those are the only two records in the table! I'm just working up the proof-of-concept at this point, preparing to implement the full functionality. Just can't understand why both queries return the same value... -Original Message- From: Bryan Stevenson [mailto:br...@electricedgesystems.com] Sent: Friday, November 05, 2010 6:49 PM To: cf-talk Subject: RE: I think I'm just making this way too hard... Without knowing what the other records in the table may be like I'd have to say there are many records that would contain a date with a day of 4 or 5. IN other words, 4 doesn't just match yesterday, it also matches the 4th day of any month. So if you have older records, that could be skewing the results. Other than that your logic seems soundsbut we don't really have the whole picture... I can say TGIF!! You can take a breather soon Rick!! ;-) Cheers ~| 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:338883 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
So much for that plan ;-) Have you tried each query with hardcoded values (i.e. stuff in the 4s and 5s where you think they should be)? If that works, then what you think you are running (when using dates and date functions) may not really be running what you think. Other than that...I'm outta ideas (and here for the day). Good luck!! 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:338884 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Thanks, Carl Ian for the latest suggestion on using the simplied dateDiff curdate. That seems to work fine. Running: select totalNew from04a_dailyNumberNewHMLSProperties where dateDiff(curdate(),dateOfTotal) = 1 does return the correct number for yesterday, 927. And running: select totalNew from04a_dailyNumberNewHMLSProperties where dateDiff(curdate(),dateOfTotal) = 0 returns the correct number for today, 901. How simple and elegant! I can feel my headache being soothed already. Time to quit for the day! Thanks, again! Rick -Original Message- From: Carl Von Stetten [mailto:cmvon...@hotmail.com] Sent: Friday, November 05, 2010 6:52 PM To: cf-talk Subject: Re: I think I'm just making this way too hard... 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 ~| 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:338885 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: I think I'm just making this way too hard...
Thanks for the suggestion. I did use hard-coded dates, but not 4's and 5's. Next step would have been that. However, per my latest post before this, I think we have a winner! :o) -Original Message- From: Bryan Stevenson [mailto:br...@electricedgesystems.com] Sent: Friday, November 05, 2010 7:15 PM To: cf-talk Subject: RE: I think I'm just making this way too hard... So much for that plan ;-) Have you tried each query with hardcoded values (i.e. stuff in the 4s and 5s where you think they should be)? If that works, then what you think you are running (when using dates and date functions) may not really be running what you think. Other than that...I'm outta ideas (and here for the day). Good luck!! 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:338886 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
