How to delete oldest week(period) in MS SQL database?

Question

MS SQL 2008R2 Express, I have database - one table with rows. And when db size became about 10gb I need to clean one oldest week in this table (I dont know the date). Could you help me with script? Thanks.

UPD1. There is column DateAndTime, so i make query like this in VBA:

strSQL = "SELECT DateAndTime
                ,TagName
                ,Val
                ,SetPoint
                ,Limit_H
                ,Limit_L
                ,Result 
          FROM dbo.Statistic 
          WHERE DateAndTime  BETWEEN CAST('" & TimeBegin & "' AS datetime) AND CAST( '" & TimeEnd & " ' AS datetime) 
          Order By DateAndTime desc , (CASE WHEN ISNUMERIC(TagName)=1 THEN CAST(CAST(TagName AS float) AS INT)END ) desc"

How do you identify "oldest week"? Do you mean this is the first data loaded and you don't have any timestamp in your table? Deleting only by metadata? — Christian4145, Sep 26 '17 at 10:24
"i dont know the date", of what? You do not have a timestamp column in your table? — Amit Kumar Singh, Sep 26 '17 at 10:35

score 2 · Accepted Answer · answered Sep 26 '17 at 10:36

2

What about this -

DELETE YOUR_TABLE
WHERE DATE_COL BETWEEN (SELECT MIN(DATE) FROM YOUR_TABLE) AND (SELECT MIN(DATE) FROM YOUR_TABLE) + 7

answered Sep 26 '17 at 10:36

Ankit Bajpai

13,128
4
25
40

`delete from` is correct syntax, and you need `dateadd()` for SQL server – JohnHC Sep 26 '17 at 10:37
1

I think `DELETE` solely will also takes care for the same. And this syntax should work though i have not tried it yet. – Ankit Bajpai Sep 26 '17 at 10:40
1

@JohnHC It works on my MSSQL db: SELECT GETDATE()-7 – etsa Sep 26 '17 at 10:40

score 1 · Answer 2 · answered Sep 26 '17 at 10:40

1

As Ankit suggested, use the min() of the date and add a week

delete 
from dbo.statistic
where dateandtime between min(dateandtime) 
                    and dateadd(week, 1, min(dateandtime))

answered Sep 26 '17 at 10:40

JohnHC

10,935
1
24
40

How to delete oldest week(period) in MS SQL database?

2 Answers2

Linked

Related