I don't *think* so (though I could be wrong).
From the original post,Quote:
I have a SQL statement that searches a date/time field in MS Access
which has data such as "12/20/2006 10:02:18 AM".
So it would appear that his data is being stored as a timestamp.
This code:
WHERE timereceived >= #createODBCDate(now()-1)# AND
timereceived < #createODBCDate(now()+1)#
Results in:
WHERE timereceived >= {d '2006-12-20'} AND
timereceived <{d '2006-12-22'}
I haven't tried it, but if the datatype on the field is 'ts' and you're
trying to compare it with just 'd' (meaning it's holding data like, {ts
'2006-12-20 13:43:24'} and you're trying to compare using {d
'2006-12-20}). I'm not positive that will work. It may. I dunno.
Also, is it faster to compare the way you suggest instead of using the
in-built SQL BETWEEN statement? I would *guess* that using BETWEEN is
faster. For small amounts of data the time difference may be
immeasurable, but over lots of records (hundreds of thousands?
millions?) the time difference might be significant... but that's just
my guess. I'm no SQL guru. Not by a long shot. :o)
Cheers,
Chris
Jim Wright wrote:
> Christopher Jordan wrote:
>
>> Simple: Because CreateODBCDateTime(now()-1) doesn't cover the proper
>> time span that he needed to cover. He needed to check for entries in the
>> database that occurred between midnight yesterday (00:00:00) and
>> 23:59:59 of today. now()-1 doesn't give that sort of control.
>>
>> Claude Schneegans wrote:
>>
>>> Gee, why not simply use this:
>>> where timereceived between #createODBCDate(now()-1)# and
>>> #createODBCDate(now())#
>>>
>>>
>
> However....
> WHERE timereceived >= #createODBCDate(now()-1)# AND
> timereceived < #createODBCDate(now()+1)#
> would cover that range.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264722
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
