Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL delete based on date

Posted on 2010-09-23
8
Medium Priority
?
381 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 setup several different housekeeping processes for a SQL Server.

618 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