>Christoper, This was really helpful, as I was scratching my head on it yesterday. I do have a question for you though: I still get the time part of it showing up. How do I get rid of that?
2007-01-24 00:00:00.0 2007-01-19 00:00:00.0 2007-01-19 00:00:00.0 2007-01-10 00:00:00.0 2007-01-02 00:00:00.0 2006-12-29 00:00:00.0 2006-12-27 00:00:00.0 2006-12-27 00:00:00.0 2006-12-27 00:00:00.0 I am using an Access database at the moment. I used what you had below, and date showed up beautifully (Many THANK YOUs for this). John Thanks ALL for your help! > > Not only now but throughout this year. > > This is a very helpful forum with developers who have a lot patience > with us newbies or semi-newbies! > > -----Original Message----- > From: Christopher Jordan [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 20, 2006 12:51 PM > To: CF-Talk > Subject: Re: time/date format in Access > > 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 > >> > >> > >> > >> > >> > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http: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:267617 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
