Search code examples
sqlsql-servervbasql-server-2008r2-express

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

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"
Solution

  • What about this - 

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