question

Shashwati Saha avatar image
Shashwati Saha asked

delete from audithis where datediff(day, dateadd(second, sttotal, '1/1/1970'), getdate()) > 30

Hello , I have to run this query to delete data from audithis table.But i dnt understand this part of this query: day, dateadd(second, sttotal, '1/1/1970'), getdate()) > 30.

Can u pls give me the explanation of the query?

sql-server-2000query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jason Cumberland avatar image
Jason Cumberland answered

Most likely the sttotal column is in EPOCH format, which is the number of seconds since 1/1/1970. The dateadd function part ( dateadd(second, sttotal, '1/1/1970') ) is converting that to a human readable time by adding those seconds back to 1/1/1970.

The rest of the query: datediff( day, dateadd(second, sttotal, '1/1/1970'), getdate()) > 30

If this is used in the where clause then this is looking for records that are older than 30 days than the current date. See the code examples below to show the sliding window this formula creates when using getdate(). Use the link to calculate the EPOCH date.

EPOCH calculator: http://www.epochconverter.com/

-- getdate() = 2010-03-16 11:55:58.417            
select            
    getdate()            
            
-- 1266339248 = 2/16/2010            
select            
    datediff( day, dateadd(second, 1266339248, '1/1/1970'), getdate())            
select            
    datediff( day, dateadd(second, 1266339248, '1/1/1970'), '2010-03-16 11:55:58.417')            
            
-- 1266166448 = 2/14/2010            
select            
    datediff( day, dateadd(second, 1266166448, '1/1/1970'), getdate())            
select            
    datediff( day, dateadd(second, 1266166448, '1/1/1970'), '2010-03-16 11:55:58.417')            
            
-- 1266080048 = 2/13/2010            
select            
    datediff( day, dateadd(second, 1266080048, '1/1/1970'), getdate())            
select            
    datediff( day, dateadd(second, 1266080048, '1/1/1970'), '2010-03-16 11:55:58.417')            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.