Solved

SQL delete based on date

Posted on 2010-09-23
8
372 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 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 250 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 59

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
 
LVL 59

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 92

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 59

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 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

10 Experts available now in Live!

Get 1:1 Help Now