RE: Baffing date problem
Thanks Bobby (and the other good folk who chipped in too) - your first
option did the trick.
Where qEvents.theDate =
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Bobby Hartsfield [mailto:bo...@acoderslife.com]
Sent: Sunday, 22 May 2011 12:41
To: cf-talk
Subject: RE: Baffing date problem
First id try something like:
Where qEvents.theDate =
If that doesn't work, Id try:
where qEvents.TheDate =
If that doesn't work... read on.
If you are on SQL Server 2008, you could try something like...
where cast(qEvents.TheDate as date) = #createODBCDate(dayview)#
Other wise one of these might work...
Where cast(datePart(year, qEvents.TheDate) as nvarchar(4)) + '/' +
cast(datePart(month, qEvents.TheDate) as nvarchar(2)) + '/' +
cast(datePart(day, qEvents.TheDate) as nvarchar(2)) = #dateformat(dayview,
"-mm-dd")#
Or maybe something goofy like...
Where qEvents.theDate = '#dayview# 00:00:00.0'
.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: Saturday, May 21, 2011 10:16 PM
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
select *
from qEvents
where qEvents.TheDate = #dayview#
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
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:344814
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Baffing date problem
you need to make the same format on both sides of the SQL where clause
so try:
On 22/05/2011 12:15, Kevin Parker wrote:
>
> I love CF but dates are definitely my Achilles heel.
>
> I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
> 00:00:00.0'}
>
> I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
>
> I would have thought that as time stamps CF would see these the same despite
> the 0 difference but the query returns no rows and I'm stumped. A cfdump of
> qEvents shows the query is returning data correctly. Funny thing is this
> works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
> What I'm trying to do is do a check for qEvents that on a given day there
> are or are not events for that day (thisday). Any advice greatly appreciated
> - thank you.
>
>
>
>
>select *
>from qEvents
>where qEvents.TheDate = #dayview#
>
>
>
--
Yours,
Kym Kovan
mbcomms.net.au
~|
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:344813
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
First id try something like:
Where qEvents.theDate =
If that doesn't work, Id try:
where qEvents.TheDate =
If that doesn't work... read on.
If you are on SQL Server 2008, you could try something like...
where cast(qEvents.TheDate as date) = #createODBCDate(dayview)#
Other wise one of these might work...
Where cast(datePart(year, qEvents.TheDate) as nvarchar(4)) + '/' +
cast(datePart(month, qEvents.TheDate) as nvarchar(2)) + '/' +
cast(datePart(day, qEvents.TheDate) as nvarchar(2)) = #dateformat(dayview,
"-mm-dd")#
Or maybe something goofy like...
Where qEvents.theDate = '#dayview# 00:00:00.0'
.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: Saturday, May 21, 2011 10:16 PM
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
select *
from qEvents
where qEvents.TheDate = #dayview#
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
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:344812
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
I tried that - but I'll have another crack - thank you!!!
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk]
Sent: Sunday, 22 May 2011 12:33
To: cf-talk
Subject: RE: Baffing date problem
Quotes around #dayview# ?
select *
from qEvents
where qEvents.TheDate = '#dayview#'
>>-Original Message-
>>From: Kevin Parker [mailto:tras...@internode.on.net]
>>Sent: 22 May 2011 03:16
>>To: cf-talk
>>Subject: Baffing date problem
>>
>>
>>
>>I love CF but dates are definitely my Achilles heel.
>>
>>I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
>>00:00:00.0'}
>>
>>I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
>>
>>I would have thought that as time stamps CF would see these the
>>same despite
>>the 0 difference but the query returns no rows and I'm stumped. A
>>cfdump of
>>qEvents shows the query is returning data correctly. Funny thing is this
>>works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
>>What I'm trying to do is do a check for qEvents that on a given day there
>>are or are not events for that day (thisday). Any advice greatly
>>appreciated
>>- thank you.
>>
>>
>>
>>
>> select *
>> from qEvents
>> where qEvents.TheDate = #dayview#
>>
>>
>>
>>++
>>Kevin Parker
>>Advanced Imaging
>>
>>e: webmas...@advancedimaging.com.au
>>w: www.advancedimaging.com.au
>>m: 0418 815 527
>>
>>++
>>
>>http://au.linkedin.com/in/krparker
>>
>>
>>
>>
>>
~|
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:344811
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
Thank you - that looks elegant - but that took a dive too -
"Encountered "DateDiff ( d ,. Incorrect conditional expression, Expected one
of [like|null|between|in|comparison] condition"
The query looks like SQL
select * from qEvents where DateDiff(d, qEvents.TheDate, {ts '2011-06-01
00:00:00'}) = 0
you can see it here if it helps
http://www.trsbok.com/_ncsi/index.cfm?month=6&year=2011
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Russ Michaels [mailto:r...@michaels.me.uk]
Sent: Sunday, 22 May 2011 12:24
To: cf-talk
Subject: Re: Baffing date problem
Kevin,
Try this.
select *
from qEvents
where DateDiff(d, qEvents.TheDate, #dayview#) = 0
--
Russ Michaels
www.bluethunderinternet.com : Business hosting services & solutions
www.cfmldeveloper.com: Free ColdFusion developer hosting
www.michaels.me.uk : my blog
www.cfsearch.com : ColdFusion search engine
**
*skype me* : russmichaels
~|
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:344810
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
Quotes around #dayview# ?
select *
from qEvents
where qEvents.TheDate = '#dayview#'
>>-Original Message-
>>From: Kevin Parker [mailto:tras...@internode.on.net]
>>Sent: 22 May 2011 03:16
>>To: cf-talk
>>Subject: Baffing date problem
>>
>>
>>
>>I love CF but dates are definitely my Achilles heel.
>>
>>I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
>>00:00:00.0'}
>>
>>I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
>>
>>I would have thought that as time stamps CF would see these the
>>same despite
>>the 0 difference but the query returns no rows and I'm stumped. A
>>cfdump of
>>qEvents shows the query is returning data correctly. Funny thing is this
>>works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
>>What I'm trying to do is do a check for qEvents that on a given day there
>>are or are not events for that day (thisday). Any advice greatly
>>appreciated
>>- thank you.
>>
>>
>>
>>
>> select *
>> from qEvents
>> where qEvents.TheDate = #dayview#
>>
>>
>>
>>++
>>Kevin Parker
>>Advanced Imaging
>>
>>e: webmas...@advancedimaging.com.au
>>w: www.advancedimaging.com.au
>>m: 0418 815 527
>>
>>++
>>
>>http://au.linkedin.com/in/krparker
>>
>>
>>
>>
>>
~|
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:344809
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Baffing date problem
Kevin, Try this. select * from qEvents where DateDiff(d, qEvents.TheDate, #dayview#) = 0 -- Russ Michaels www.bluethunderinternet.com : Business hosting services & solutions www.cfmldeveloper.com: Free ColdFusion developer hosting www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| 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:344808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
select *
from qEvents
where qEvents.TheDate = #dayview#
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
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:344807
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
