I want to delete all less two days before actual date

Ramon Quintana

8y817

hi, i need to left only records in my table with two days before actual date:

I try this:

delete from dbo.prueba where DATE = (select GETDATE()) -2

but NOTHING HAPPENED

Please help me

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Madan Shekar7y ago
Accepted answer
delete from vtevents where CAST(createdat AS DATE) != DATEADD(dd,-2,CAST(getdate() as DATE))
Ramon Quintana7y ago
Hi You Frinds,

it works with this:

DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) < -1

DONE!!!

Thanks people..\m/
Ramon Quintana7y ago
Hi You Friend,

it works with this:

DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) < -1

DONE!!!

Thanks people..\m/
Suraj Kumar7y ago
Thanks for your reply and try again and again to get rid into your query. It is a good idea to learn.
Ramon Quintana7y ago
Hi You Frinds,

it works with this:

DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) < -1

DONE!!!

Thanks people..\m/
Jayesh Vaghasiya7y ago
try this

declare @day int = -2
DECLARE @TBL TABLE(ID INT,EDATE DATE )
INSERT INTO @TBL(ID,EDATE) VALUES(1,'2018-10-21')
INSERT INTO @TBL(ID,EDATE) VALUES(2,'2018-10-22')
INSERT INTO @TBL(ID,EDATE) VALUES(3,'2018-10-23')
INSERT INTO @TBL(ID,EDATE) VALUES(4,'2018-10-24')
INSERT INTO @TBL(ID,EDATE) VALUES(5,'2018-10-25')
INSERT INTO @TBL(ID,EDATE) VALUES(6,'2018-10-26')
INSERT INTO @TBL(ID,EDATE) VALUES(7,'2018-10-27')
delete FROM @TBL WHERE EDATE = CONVERT(DATE,DATEADD(DAY, @day, GETDATE()))
Suraj Kumar7y ago
You can easily check whether your table contains any records in EVT_DATE with values which is: 2018-10-25 which is the output of second where condition. If any records exist with the below values then it will delete that records otherwise it will not delete any records.
SELECT CONVERT(VARCHAR,GETDATE(),112)
As I said earlier without removing time part it is hard to delete the row as it gives every time different output.
SELECT DATEADD(DAY, -2, GETDATE())
Cuurently its output is coming as: 2018-10-23 18:56:52.000 but when you will run this query the output will come different as time will change continuously.
If you still want to delete or select some record then please give me some date value and mark which records you want to delete. I will try to help you.

Hope this will help you.
Ramon Quintana7y ago
Hi Suraj,

can you guide me in why this sentence doesn´t work?

delete from dbo.prueba where EVT_DATE = (SELECT DATEADD(DAY, -2, GETDATE()))

It compile without any error or warnings but doesn´t delete nothing

niether this:

delete from dbo.prueba WHERE CONVERT(VARCHAR,DATEADD(DD,-1,EVT_DATE),112) = CONVERT(VARCHAR,GETDATE(),112)
Suraj Kumar7y ago
You need to understand first how to add or subtract number of days to a date field using DATEADD function. Now as you are saying that the format in both date one from getdate() and other from tabe's date field are the same. But the time is different so when comparing both date it will result false due to the different time part.
IF('2018-10-23 15:41:24.097' = '2018-10-23 10:48:06.000')
BEGIN
    SELECT 'Record Matched'
END
ELSE
BEGIN
    SELECT 'Record Not Matched'
END
So, when comparing with date most of time we remove time part from the date value. Your try was going correctly which needed some more effort and correction.To know more about dateadd() function please refer the below article link
https://www.c-sharpcorner.com/UploadFile/rohatash/dateadd-function-in-sql-server-2012/

Hope this will help you.
Ramon Quintana7y ago
Ok, thats fine but my table´s column has the same date format than the showed by the GetDate

2018-10-23 15:41:24.097 -----GetDate

2018-10-23 10:48:06.000 ----- Table´s Column

In resume, i only want left records from today and yesterday, and delete all the month´s rest .

somethig like:

delete from dbo.prueba where DATE = (select GETDATE()) -2
Suraj Kumar8y ago
You can learn about date format in sql server here

https://www.c-sharpcorner.com/blogs/how-to-format-datetime-date-in-sql-server-2005
Suraj Kumar8y ago
It is the format of date suppose your table date column value is - 2018-10-24 21:50:44.327
and you are comparing with 2018-10-24 then it will not match.

So, 112 is used with convert varchar to remove the time and separator from date. Always remeber both side the format should be equal in comparision with date format.
SELECT CONVERT(VARCHAR,GETDATE(),112) AS TODAYDATE
The output of the above query will be - 20181024 which is year, month and date.
Ramon Quintana8y ago
What does the ´112´ value?
Suraj Kumar8y ago
Hello Ramon,
You need to write where condition as shown below
WHERE CONVERT(VARCHAR,DATEADD(DD,-2,CREATIONDATE),112) = CONVERT(VARCHAR,GETDATE(),112)
Here creationdate is the name of my column and you need to update it with your column name.

Hope this will help you.