Oh! Duh... hadn't thought of that. *smack*
In that case:
<cfset today = now()>
<cfset today = CreateDateTime(year(today), month(today), day(today),
"23", "59", "59")>
<cfset yesterday = DateAdd("d", -1, today)>
<cfset yesterday = CreateDate(year(yesterday), month(yesterday), day
(yesterday))>
<cfquery name="get" datasource="barracuda_log">
SELECT *
FROM message_log_20391
WHERE timereceived BETWEEN #Yesterday# AND #Today#
</cfquery>
There, give that a go.
Cheers,
Chris
Orlini, Robert wrote:
> Hi Chris,
>
> Thanks for the info!
>
> ....only I still get 0 records. It seems it's trying to search a specific
> time now:
>
> where timereceived between {ts '2006-12-19 11:26:17'} and {ts '2006-12-20
> 11:26:17'}
>
> Anyway to ignore/remove the time?
>
>
> -----Original Message-----
> From: Christopher Jordan [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 20, 2006 11:16 AM
> To: CF-Talk
> Subject: Re: time/date format in Access
>
> Hi Robert,
> I think the problem is that your dates are not in ODBC date format.
>
> Try something like this:
>
> <cfset Today = now()> <!--- should automatically be in ODBC date format --->
> <cfset Yesterday = DateAdd("d",-1,Today)> <!--- Notice it's 'd' not 'dd'
> --->
>
> <cfquery name="get" datasource="barracuda_log">
> SELECT *
> FROM message_log_20391
> WHERE timereceived BETWEEN #Yesterday# AND #Today#
> </cfquery>
>
> I think this will work for you.
>
> Cheers,
> Chris
>
> Orlini, Robert wrote:
>
>> 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". I want to display dates between
>> the current day and one day behind.
>>
>> This statement keeps giving me 0 records.
>>
>> <cfset month = #dateformat(now(),"mm")#>
>> <cfset year = #dateformat(now(),"yyyy")#>
>> <cfset year2 = #dateformat(now(),"yyyy")#>
>> <cfset month2 = #dateformat(now(),"mm")#>
>> <cfset day = #dateformat(now(),"dd")#>
>> <cfset day2 = DateFormat(Now(),"dd")-1>
>>
>> <CFSET today = Createdate(Year,Month,day)>
>> <CFSET onedaybehind = Createdate(Year2,Month2,day2)>
>>
>> <CFQUERY name="get" datasource="barracuda_log">
>> SELECT * FROM message_log_20391
>> where timereceived between #onedaybehind# and #today#
>>
>> I also tried this suggestion:
>> <CFSET today = Createdatetime(Year,Month,day, 23, 59, 59)>
>> <cfset onedaybehind = createDateTime(year2, month2, day2, 0, 0, 0) />
>>
>> But still 0 records.
>>
>> Any suggestions would be appreciated.
>>
>> Thanks!
>>
>> Robert O.
>> HWW
>>
>>
>>
>>
>>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:264626
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
