?
Solved

SQL query to delete records by date

Posted on 2007-12-04
4
Medium Priority
?
7,313 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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