SQL Server agent deletes records at midnight

Question

I'm kinda new to SQL so I apologize in advance if this question is not very clear. I want to create a job in SQL Server Agent that deletes a row after exactly 24 hours. My problem is the SQL Server Agent deletes the record at midnight rather than after 24 hours, for example, if I create a record at 3 PM the record will be deleted at 12 AM rather than the next day at 3 PM.

Is there anything that can fix this.

Thanks for the help

Without showing us your code, or sample data, we can only guess. But I assume you're not considering the time, — Thom A, Nov 17 '19 at 13:11

Gordon Linoff · Answer 1 · 2019-11-17T13:30:44.883

Don't take this approach! Use a view and then let a job clean up afterwards.

-- Have an expiration column in the table
create table t (
    . . .
    expire_datetime datetime
);

-- Filter out the unexpired rows for the view
create view v_t as
    select t.*
    from t
    where expire_datetime > getdate();

You can set expire_datetime when you create the record. Or, if you prefer, it could simply be the creation datetime plus 24 hours:

alter table t add expire_datetime as (dateadd(hour, 24, createdAt));

Do all your access through the view. Then, you can periodically run a job to delete the expired records. You can do this once day, once an hour, once a week -- whenever.

SQL Server agent deletes records at midnight

1 Answers1