?
Solved

SQL delete based on date

Posted on 2010-09-23
8
Medium Priority
?
380 Views
Last Modified: 2012-05-10
We have an application that collects sample data. From SQL Express Studio I need to delete all data from the sample table that is older than a certain date. I tried using

DELETE FROM Samples
Where CreateDate < 01/01/2008

I was returned a value that 0 rows were affected. What is the syntax I should be using to delete data that has a create date older than 01/01/2008?

0
Comment
Question by:bkpierce
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 33748349
--Delete older than 2008-01-01:DELETE FROM SamplesWhere CreateDate < '2008-01-01'--Delete newer than 2008-01-01:DELETE FROM SamplesWhere CreateDate > '2008-01-01'
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 1000 total points
ID: 33748392
DELETE FROM Samples
Where CreateDate < 01/01/2008

Means DELETE where CreateDate is less than
1 / 1 / 2008 ~= 0.0005
But because you are doing integer division, the result is 0.
0 is equal to the date 1900-01-01

So it is really

DELETE FROM Samples
Where CreateDate < '1900-01-01'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33748411
I agree with matthewspatrick, but just note the issue is the missing ' -- though I agree that the ODBC / ISO format YYYY-MM-DD is best and will avoid some formatting issues like figuring out if 08/09/10 is August 9th or September 8th.  Just clarifying that you can fix your statement as is by adding the quotes.

DELETE FROM Samples
Where CreateDate < '01/01/2008'

Please award matthewspatrick for first correct answer, though. Thanks!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33748432
Yeah, what cyberkiwi said! Okay, guess our job here is done, Patrick provided the answer and you and I explained to death, Richard. Good work, team. See you guys!

Best regards and happy coding, bkpierce,

Kevin
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33748434
No objection to a split here: cyberkiwi and mwvisa1 provided valuable insight on the cause of the problem, which I was being too lazy to explain :)
0
 

Author Closing Comment

by:bkpierce
ID: 33748443
Thank you all, yes it was just the quote I was missing. I split the points since I figured you two probably answered at the same time.
0
 

Author Comment

by:bkpierce
ID: 33748450
mwvisa1 if you want points i can request moderator to open so I can do a 3 way split
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33748641
No I am fine as I didn't see cyberkiwi's post which says the essence of what I commented for which is the quotes made this evaluate as math instead of literal date.

Glad you got what you needed and so no worries on changing the disposition.

Regards,
Kevin
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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