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

delete all records except the last 2 days using sql

I am trying to delete everything from my table except the last 2 days; also excluding today's data.  the data type for the date field is datetime.
0
moe57
Asked:
moe57
  • 4
  • 4
1 Solution
 
UnifiedISCommented:
DELETE FROM table
WHERE datefield < CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 101)
0
 
moe57Author Commented:
it is not deleting anything.  All the data are still there.  Here is how the data look like
2015-03-06 20:30:00.000
2015-03-06 21:00:00.000
2015-03-06 21:30:00.000
2015-03-06 22:00:00.000
2015-03-06 22:30:00.000
2015-03-06 23:00:00.000
2015-03-06 23:30:00.000
2015-03-05 00:00:00.000
2015-03-05 00:30:00.000
2015-03-05 01:00:00.000
2015-03-05 01:30:00.000
2015-03-05 02:00:00.000
2015-03-05 02:30:00.000

Open in new window

0
 
Scott PletcherSenior DBACommented:
If the table is not too large, you can delete them directly:


DELETE FROM table_name
WHERE datetime_column < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 2, 0)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
UnifiedISCommented:
Those dates are from the last 2 days which you said you wanted to retain. "Everything...except the last 2 days"
0
 
moe57Author Commented:
thanks
0
 
UnifiedISCommented:
Was there something wrong with my proposal?
0
 
moe57Author Commented:
UnifiedIS,
No nothing was wrong your proposal and i meant to give you all the points.  I thought you were the only one who answered my questions and i did not look at the names.  Sorry but how can i correct my mistake now?
0
 
UnifiedISCommented:
I think EE has to reopen it once it's been solved but I don't see the link for it.  It's not that big of a deal, I'll survive without the points. I'm glad we were able to help you.
0
 
moe57Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now