Thanks Rich. Tried to implement but recevd errors with Convert. Where does this command go pls? In the where? Tried that but still got errors.
-----Original Message----- From: Richard Kroll [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 11:33 AM To: CF-Talk Subject: RE: date compare Firstly, what you have to keep in mind is that you said you have "date/time" fields. When you are trying: WHERE myDate = '03/26/2007' what you are really saying is: WHERE myDate = '2007-03-26 00:00:00.000' This comparison will fail on a record such as '2007-03-26 17:56:22.000'. To get around that you can convert the date/time field to get it to drop the time: CONVERT(char(8), myDate, 1) will give you '03/26/07' > <cfquery name="Getnow" datasource="trials"> > Select * from trials_info > </cfquery> > > <CFSET sevendaysback = DateAdd("d", -7, getnow.expiration)> > > Select * from trials_info > Where '#DateFormat(CreateODBCDate(now()), "mm/dd/yyyy")#' = > '#DateFormat(CreateODBCDate(sevendaysback), "mm/dd/yyyy")#' > </cfquery> I get from your code that you are trying to find all records that have an expiration column equal to 7 days ago. You could do this all in one query like the following (MSSQL syntax): SELECT * FROM trials_info WHERE CONVERT(char(8), dateadd(dd, -7, expiration), 1) = CONVERT(CHAR(8), getDate(), 1) HTH, Rich Kroll ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273757 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
