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, "yyyy-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
