I want to delete all less two days before actual date

Question

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

Madan Shekar · Answer

delete from vtevents where CAST(createdat AS DATE) != DATEADD(dd,-2,CAST(getdate() as DATE))

Jayesh Vaghasiya · Answer

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()))

Ramon Quintana · Answer

Hi You Frinds, it works with this: DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) DONE!!! Thanks people..\m/

Ramon Quintana · Answer

Hi You Friend, it works with this: DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) DONE!!! Thanks people..\m/

Suraj Kumar · Answer

Thanks for your reply and try again and again to get rid into your query. It is a good idea to learn.

Ramon Quintana · Answer

Hi You Frinds, it works with this: DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) DONE!!! Thanks people..\m/

Suraj Kumar · Answer

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 Quintana · Answer

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 Kumar · Answer

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 Quintana · Answer

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 Kumar · Answer

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 Kumar · Answer

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 Quintana · Answer

What does the ´112´ value?

Suraj Kumar · Answer

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.

I want to delete all less two days before actual date

Answers (14)