Clear data every so often

Question

Clear data every so often

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have a table created in SQL Server which has the following data:

CREATE TABLE [dbo].[LogData](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fechahora] [text] NULL,
    [descripcion] [text] NULL,
    [servidor] [text] NULL,
    [horaCaptura] [time](7) NULL,
    [fechaCaptura] [date] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This table already contains data, but it is linked to an application developed in Python, my users use this table once a day and save more than 14000 records in a day, so in a month it will take up a lot of space in the HDD, and I want to formulate a Procedimiennto almacenado that I delete these records every 90 days, I have not managed to do it and use DATEADD in the same way and try with a simple elimination of dates but that is what I do not want because it would be very tedious what I have of procedure is this:

CREATE PROCEDURE dbo.Sp_ProcesoDiario
AS
BEGIN
  DECLARE @tiempo date = DATEADD(day,90,fechaHora)
--DELETE FROM LogData WHERE fechaCaptura <= GETDATE();
  DELETE FROM LogData WHERE @tiempo >= GETDATE();

END

I would greatly appreciate it in advance. :)

sql-server

asked by Santiago Huh 18.07.2017 в 19:31

source

2 answers

0

I went around the internet a lot and found ways to use the dateadd code better and with the help of the @Patricio Moracho response I managed to create a code that if I work:

CREATE PROCEDURE dbo.Sp_EliminacionDias90
AS
BEGIN
    DECLARE @lintUltimoRegistro int
    DECLARE @ldtFechaDiaAnterior datetime
    SET @ldtFechaDiaAnterior  = dateadd(minute,-5, getdate())

    SELECT @lintUltimoRegistro = Max(id) From LogData
    WHERE CONVERT(char(10), getdate(), 112) = CONVERT(char(10), @ldtFechaDiaAnterior, 112)

    DELETE FROM LogData WHERE CONVERT (char(10), getdate(), 112) = CONVERT (char(10), @ldtFechaDiaAnterior, 112)

END

answered by 18.07.2017 в 20:07

Does not validate conditional with array in php Infinite loop in Javascript

score 2 · Accepted Answer

Santiago, it's a simple problem if you use the DATEADD function , I pass the logic to you, then you add it to SP

DECLARE @Now    DATETIME

SELECT  @Now    = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))

DELETE FROM LogData WHERE fechaCaptura < DATEADD(DAY, -90, @Now)

Some comments

It is preferable to use a variable @now for GETDATE() , in this way you can keep a single value if you need to reuse it. For example: if you have several operations where you register an execution date, it is usually preferable that they all have the same date.
The CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112)) is simply a trick to remove the part of the time of the date, which will make sense when we use it to calculate from which date we are going to debug.
Finally we use DATEADD with the value negative of the days so that from today we calculate what is the maximum date to debug.