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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273728
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
