SQL Server - Update table based on column of datetime

Question

I'm trying to create table where I store some data and one of the columns is "Start Time"(C# long in milliseconds). I want to remove all records from that table where Start Time is more than month ago via trigger or something like that. Is this possible and how can I achieve it?

EDIT: Transformation between long and DateTime is not a problem. It was more likely how to make this database to automatically clean that table with some kind of a trigger. I will transform it to DateTime if it is required.

@ZoharPeled Storing timestamps as long UNIX epoch time is a completely valid thing to do, and there are reasons for why someone would want to do that. — Tim Biegeleisen, Oct 25 '18 at 08:19
@TimBiegeleisen I'm well aware of unix time and valid reasons for using it, however it's not clear if the OP is aware of that or is just using the wrong data type (like the rest 8 out of 10 cases) when datetime is stored in any data type that isn't datetime. — Zohar Peled, Oct 25 '18 at 10:21
I'm totally aware, still it is easier to do it that way in my case for some reasons — Iavor Orlyov, Oct 25 '18 at 10:54

SAEED REZAEI · Accepted Answer · 2018-10-25T08:47:35.000

4

Keep the date in the DateTime Type field. Work that is scheduled to be done periodically is best avoided through the trigger. The use of schedule jobs is suggested. The use of it is described in the following answer. https://stackoverflow.com/a/5471440/3600957

edited Oct 25 '18 at 08:47

answered Oct 25 '18 at 08:41

SAEED REZAEI

449
3
6

score 1 · Answer 2 · answered Oct 25 '18 at 08:18

Assuming your [Start Time] column contains milliseconds since the UNIX epoch, then you may convert to a SQL Server datetime using:

DATEADD(s, [Start Time]/1000, '1970-01-01')

To delete all records older than one month, we can try:

DELETE
FROM yourTable
WHERE DATEADD(s, [Start Time]/1000, '1970-01-01') < DATEADD(month, -1, GETDATE());

SQL Server - Update table based on column of datetime

2 Answers2