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>
How is this going to work on the first day of the month?
> <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#
Have you ever looked at the debug output of the query to see why it didn't
return anything?
How about:
<cfset today = CreateDate(Year(Now()),Month(Now()),day(Now()))>
<cfset yesterday = DateAdd("d", -1, today)>
<CFQUERY name="get" datasource="barracuda_log">
SELECT *
FROM message_log_20391
WHERE timereceived
BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#yesterday#">
AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#today#">
</cfquery>
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:264610
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
