truncating data older than 30 days

[sqldba]

Hi,

I have a table with
C1- ID
C2-Date
C3-Date with time
C4-quantity

I need to create a daily process that will remove the oldest data(data older than 30 days) on the table and make sure the table is with only 30 days worth of data.


Please help me.

Thanks in advance

[ck9663]

Hi,

I have a table with
C1- ID
C2-Date
C3-Date with time
C4-quantity

I need to create a daily process that will remove the oldest data(data older than 30 days) on the table and make sure the table is with only 30 days worth of data.


Please help me.

Thanks in advance

based on which datecolumn?


assuming c2
DELETE FROM myTable where datediff(day,c2, getdate()) > 30

couple of notes:
1. you might not be able to recover these records.
2. you may add a column to flag if the record is valid or not and just use that instead.
3. backup this table, and delete the records.

-- CK