SQL Date Comparison Problem

[TechDude]

Hi all,
I am attempting to run a sql query to select records with a date older than 30 days ago. I don't get any errors but it doesn't return any results when I know that there are two results that should be returned. The date format in the database is mm/dd/yyyy. I'm using access 2000. Can anyone offer any advice as to why this isn't working?
thanks,
-Chris

the code is:
<!--- this creates a date variable for 30 days ago--->
<cfparam name=&quot;epoch&quot; default=&quot;&quot;>
<cfset epoch = dateformat((now()- createtimespan(30,0,0,0)),&quot;mm/dd/yyyy&quot>
<!--- run query against date--->
<cfquery name=&quot;deleteOld&quot; datasource=&quot;Easylink_cust_service&quot;>
select * from contentObjects
WHERE visible = 0
AND DateDeleted < #epoch#

</cfquery>
<!--- output results --->
<cfoutput>Today:#dateformat(now(),&quot;mm/dd/yyyy&quot#, 30 days ago: #epoch# <br> </cfoutput>
<cfoutput query=&quot;deleteOld&quot;>
#DateDeleted#, #datecompare(DateDeleted,epoch)# <br>
</cfoutput>

Chris Sorel
chris@exnihilo.com

http://www.exnihilo.com

Remember, If you continue to do what you have always done,

you will continue to get what you have always gotten.

 

[tleish]

Try using the DateAdd() function:

=== START CODE EXAMPLE ===
[COLOR=666666]<!--- this creates a date variable for 30 days ago--->[/color]
<cfparam name=&quot;epoch&quot; default=&quot;&quot;>
<cfset epoch = DateAdd(&quot;d&quot;, -30, Now())>
[COLOR=666666]<!--- run query against date--->[/color]
<cfquery name=&quot;deleteOld&quot; datasource=&quot;Easylink_cust_service&quot;>
select * from contentObjects
WHERE visible = 0
AND DateDeleted < #CreateODBCDate(epoch)#

</cfquery>
[COLOR=000080]<br>[/color]
[COLOR=000080]<br>[/color]

[COLOR=666666]<!--- output results --->[/color]
<cfoutput>
Today:#dateformat(now(),&quot;mm/dd/yyyy&quot;)#,
30 days ago: #DateFormat(epoch,&quot;mm/dd/yyyy&quot;)# [COLOR=000080]<br>[/color]
</cfoutput>

<cfoutput query=&quot;deleteOld&quot;>
#DateDeleted#, #datecompare(DateDeleted,epoch)# [COLOR=000080]<br>[/color]
</cfoutput>
=== END CODE EXAMPLE === - tleish

 

[TechDude]

That did it, Much thanks!
Chris Sorel
chris@exnihilo.com

http://www.exnihilo.com

Remember, If you continue to do what you have always done,

you will continue to get what you have always gotten.