Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Trying to delete from database on datetime criteria

Posted on 2014-02-10
3
272 Views
Last Modified: 2014-02-10
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
Comment
Question by:jdthedj
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39848348
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
 
LVL 3

Author Closing Comment

by:jdthedj
ID: 39848368
Thanks agx!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39848377
Welcome :)
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question