Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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?
0
jdthedj
Asked:
jdthedj
  • 2
1 Solution
 
_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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now