asked on

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.

ASKER CERTIFIED SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

moe57

ASKER

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

Scott Pletcher

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)

UnifiedIS

Those dates are from the last 2 days which you said you wanted to retain. "Everything...except the last 2 days"

moe57

ASKER

thanks

UnifiedIS

Was there something wrong with my proposal?

moe57

ASKER

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?

UnifiedIS

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.

moe57

ASKER

thanks