Solved

SQL query to delete records by date

Posted on 2007-12-04
4
7,306 Views
Last Modified: 2012-05-05
I am looking to write an SQL query that will delete all records either by a single date or between a range of dates.  What is the proper way to write these queries?

TIA for any help!
0
Comment
Question by:dstjohnjr
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20406651
DELETE yourtable
WHERE yourfield >= convert(datetime, '2007-12-04', 120)
AND yourfield < dateadd(day, 1, convert(datetime, '2007-12-04', 120))
0
 

Author Comment

by:dstjohnjr
ID: 20406844
Thanks for the reply angellll.

A couple of clarifications:

it should be DELETE FROM yourtable, correct?

Also, can you please explain what the numbers 120 you've used and 1 (before the convert in the last line) are used for?

Thanks again!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20406886
120 stands for the style YYYY-MM-DD
the 1 stays for the 1 day to be added to the date value.

see, when you use 2007-12-04 for both start and end date, it would not return/delete any records, you have to add 1 to the "end date" when comparing using <.
0
 

Author Comment

by:dstjohnjr
ID: 20407107
Thanks Angellll!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 7 98
SQL Server 208R2 not recognizing DBF file in linked Server 11 52
configure service broker on all databases 2 78
Need help debbuging stored procedure 21 35
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now