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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
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
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
RE: Baffing date problem
Quotes around #dayview# ?
cfquery name=ForToday dbtype=query
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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
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
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=6year=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
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# ?
cfquery name=ForToday dbtype=query
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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
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
First id try something like:
Where qEvents.theDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay# /
If that doesn't work, Id try:
where qEvents.TheDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#createODBCDateTime(dayview)# /
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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
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
you need to make the same format on both sides of the SQL where clause
so try:
cfset dayview = #createODBCDate(createdate(year, month, thisday))#
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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
--
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
Thanks Bobby (and the other good folk who chipped in too) - your first
option did the trick.
Where qEvents.theDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay#
++
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 = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay# /
If that doesn't work, Id try:
where qEvents.TheDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#createODBCDateTime(dayview)# /
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.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
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
