CODEWITHSUNDEEP
sql
Hakan
Hakan

Reputation: 141

Date issue in sql

I have a table that I am entering data into every day. But I don't want that this data to be modified by other users. So I am backing up this data to another table using:

INSERT INTO  tbl_cancel_backup 
  SELECT tbl_cancel.[cdate] AS 'cdate',
         tbl_cancel.[machine_no] As 'No', 
         Sum(tbl_cancel.[amount]) AS 'Total' 
    FROM tbl_cancel 
   WHERE tbl_cancel.[Cdate]=@canceldate 
GROUP BY tbl_cancel.[Machine_no], tbl_cancel.[cdate];

After this operation, I would like to delete records which are 2 days earlier. How I have to modify below code to proper working one? 

DELETE FROM tbl_cancel WHERE cdate = CONVERT (date, GETDATE()-2);

Thanks to everyone.

Upvotes: 2

Views: 98

Answers (3)

Matthew Jones
Matthew Jones

Reputation: 26190

You could try using the DATEADD syntax: 

DELETE FROM tbl_cancel WHERE cdate <= CONVERT (date, DATEADD(dd,-2,GETDATE());

Upvotes: 0

heisenberg
heisenberg

Reputation: 9759

delete from tbl_cancel where cdate <= dateadd(d,-2,getdate())

Upvotes: 1

Nate Zaugg
Nate Zaugg

Reputation: 4218

You want to use DateAdd T-SQL

http://msdn.microsoft.com/en-us/library/ms186819.aspx

DELETE FROM tbl_cancel WHERE cdate <= DATEADD(d, -2, GETDATE())

Upvotes: 1

Related Questions