I need to remove some records

Ramon Quintana

7y902

I Could remove records from today and yestarday from a table that has a DATE Field using this sentence:

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

But now I need to remove some records from a table VFS_INTRESS than doesn´t has a DATE field,

for example this:

I need to left only records from today and yesterday, how can do that?

Thanks...

6 Replies

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

Suraj Kumar7y ago
Accepted answer
This is not feasible without creating date column. You can create a separate table with FK as I said earlier. In log table you can insert data using trigger on insert or update operation as you needed.
Sourav Mukherjee7y ago
Can you figure out by anyway which records are created today / yesterday? If yes, and if those are in sequence, you may also create another table with identity and delete records which are outside the today/yesterday's value ranges.
Ramon Quintana7y ago
Hi Amit,

I would like to know how to catch the DATE TIME when records were created in a Table without DATE Field??

for example using the Sysdate or something like

thanks,
Ramon Quintana7y ago
*Alternatively if you do not want to add any column then you can create a new separate table with foreign key and a datetable with create date. Then you can delete the record after joining that table with foreing key.

Hi Suraj,

That´s just what i meant, I want to delete records joining some existing table with foreing key where i can catch some DATE COLUMN with

Or, i would like how to catch the DATE TIME when records were created in a Table without DATE Field??

Thanks,
Amit Gupta7y ago
Oh yeah @suraj, good catch...

Ramon is asking to delete that was not present (how can this be possible @Ramon.

Please be assure and specific of what you are asking
Suraj Kumar7y ago
I think date column is not present in the table so it can not be deleted based on the date parameter or date condition. You can either think about deleting based on the another column or you can add an additional column such as createddate with default value as current datetime. Then you can delete records based on that date field.

If you want to add a column with default date column you can use below query.
ALTER TABLE YourTable ADD CreatedDate datetime default GETDATE();
Alternatively if you do not want to add any column then you can create a new separate table with foreign key and a datetable with create date. Then you can delete the record after joining that table with foreing key.
Hope this will help you.