• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

SQL delete based on date

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
bkpierce
Asked:
bkpierce
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Patrick MatthewsCommented:
--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
 
cyberkiwiCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
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
 
Patrick MatthewsCommented:
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
 
bkpierceAuthor Commented:
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
 
bkpierceAuthor Commented:
mwvisa1 if you want points i can request moderator to open so I can do a 3 way split
0
 
Kevin CrossChief Technology OfficerCommented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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