Solved

SQL query to delete records by date

Posted on 2007-12-04
4
7,311 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 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 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

731 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