I just want to run a query that compares a column value from today
to the column value of yesterday on a MySQL 5 database table.
I run:
select totalNew
from 04a_dailyNumberNewHMLSProperties
where '#dateFormat(datetime, 'mmm d, ')#' =
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
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
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,
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)
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
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
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'
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
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()
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
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
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
[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
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
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
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
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)
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
...@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
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
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
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
23 matches
Mail list logo
