Trying to delete from database on datetime criteria

I am trying to delete all records from a table that are older than 12 months.  The date field is a datetime field.  I have the following code:

<CFSET DelDate = DateAdd("yyyy",-1,Now())>

<CFQUERY NAME="DelLogs" DATASOURCE="JobOrder#Sfx#">
  DELETE FROM JOLog
  WHERE TDate <= <CFQUERYPARAM Value="#DelDate#"  CFSQLTYPE="CF_SQL_DATETIME">
</CFQUERY>

When I run the code nothing is deleted.  What am I doing wrong?
LVL 3
jdthedjAsked:
Who is Participating?
 
_agx_Commented:
EDIT:
Assuming you've verified the table actually contains records older than 1 year ago...

          SELECT COUNT(*) AS TotalMatchingRecords
          FROM    JOLog
          WHERE  TDate <= <CFQUERYPARAM Value="#DelDate#"  CFSQLTYPE="CF_SQL_TIMESTAMP">

Open in new window


If you wish to include the current time, ie remove all records earlier than 2014-02-10 12:03:15 PM use:

....
WHERE TDate <= <CFQUERYPARAM Value="#DelDate#"  CFSQLTYPE="CF_SQL_TIMESTAMP">

Open in new window


To use the date only, ie remove all records earlier than 2014-02-10

....
WHERE TDate < <CFQUERYPARAM Value="#DelDate#"  CFSQLTYPE="CF_SQL_DATE">

Open in new window

0
 
jdthedjAuthor Commented:
Thanks agx!
0
 
_agx_Commented:
Welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.