Improve company productivity with a Business Account.Sign Up

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

SQL query to delete records by date

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
dstjohnjr
Asked:
dstjohnjr
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DELETE yourtable
WHERE yourfield >= convert(datetime, '2007-12-04', 120)
AND yourfield < dateadd(day, 1, convert(datetime, '2007-12-04', 120))
0
 
dstjohnjrAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dstjohnjrAuthor Commented:
Thanks Angellll!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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