This is the table that I have created.

CREATE TABLE request
(
    bloodgrp varchar(20),
    Datetime1 date,
    check (bloodgrp in ('A ', 'A-', 'B ', 'B-', 'AB ', 'AB-', 'O-', 'O ')),
);

Then I have successfully inserted one row. How to delete this row after 1 day?

INSERT INTO Request (bloodgrp, Datetime1) 
VALUES ('A ', GETDATE())

Here is the delete query:

DELETE FROM request 
WHERE Datetime1 < GETDATE() - INTERVAL '1' DAY ;

CodePudding user response：

Create JOB Step by step

1. in SQL management studio, right click on "SQL Server Agent" under the SQL server which you are connected to.
2. Select New Job.
3. Enter the job name and then click on steps
4. Click on "New" which should be right at the bottom of the screen. Enter step name.
5. Type: keep it selected as Transact SQL
6. Enter : EXECUTE dbo.Delete Procedure here;

Now save it and it should be ready for running manually. If you do want to automate it then open the job by going into the job monitor under SQL Server Agent in SQL management studio and then click on schedule and provide when and how often you would like your job to run.

Note : You can Set Many option to run job it's depended your logic, you can run schedule date time with system parameter.

CodePudding user response：

We don't need to do any job like SQL Server agent

SELECT * from table WHERE Datetime1 > DATEADD(day, -1, GETDATE())