Solved

SQL delete based on date

Posted on 2010-09-23
8
374 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Text file into sql server 5 28
SQL Syntax: How to force case sensitive query? 2 32
access query to sql server 3 22
Job - date manual 1 22
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

831 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