delete rows less than 30 days

Question

Post reply

delete rows less than 30 days

lostdba

Old Hand

Points: 341
More actions
May 6, 2005 at 1:42 pm

#66957

Hi everyone, I'm wondering if someone could help me with a script. I need to delete all rows in a table less than 30 days from todays date, but the date is stored as a decimal. Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mark_yelton SSC Enthusiast Points: 119 More actions · Answer 1

mark_yelton

SSC Enthusiast

Points: 119

May 6, 2005 at 2:01 pm

#557461

Can you post an example of the data?

lostdba Old Hand Points: 341 More actions · Answer 2

Heres a copy of the column in question.

AUDITTIMESTAMP

1087846893579

1087924923734

1104764681188

1104732392516

1087846893610

1104741005453

1087846893657

1104693744344

1104741246891

1087846893735

I'm using this statement to see the dates, but I can't get it to work with deleting any records over 30 days old.

SELECT DATEADD(hh, - 05, DATEADD(ss, AUDITTIMESTAMP / 1000, '01/01/1970')) AS ts, *

Thanks

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

Hi a question relating to your data. You have the Col name listed as AuditTimeStamp. Is this data of type TimeStamp or rowversion? If so the following from BOL maybe of some help. TimeStamp in T_SQL is not the same as TimeStamp in SQL 92 which relates to the DateTime Data Type.

HTH

Mike

From BOL

timestamp

Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

lostdba Old Hand Points: 341 More actions · Answer 4

Hi Michael, thanks for your reply, the column is stored as a decimal, but one of our developers came up with a solution.

select * from tablename

where audittimestamp <

(

( cast (

( datediff(day,'01/01/1970',(

(

ltrim(rtrim(cast(Year(getdate() - 30) as char)))

+ '/'

+ ltrim(rtrim(cast( Month(getdate() - 30) as char)))

+ '/'

+ ltrim(rtrim(CAST((DAY(getdate() -30) - DATEPART (weekday,getdate() -30)) as char)))

)

&nbsp

) AS decimal(9,0)

&nbsp

&nbsp *86400000

)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

If DATEADD(hh, - 05, DATEADD(ss, AUDITTIMESTAMP / 1000, '01/01/1970')

returns the correct date (seems like this data is from a legacy system!)

then you could do the following

WHERE DATEDIFF(day,DATEADD(hh, - 05, DATEADD(ss, AUDITTIMESTAMP / 1000, '01/01/1970')),GETDATE()) > 30

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

or this might even work

DECLARE @AUDITTIMESTAMP bigint

SET @AUDITTIMESTAMP = SELECT CAST(DATEDIFF(day,'01/01/1970',DATEADD(day,-30,GETDATE())) as numeric) * 24 * 60 * 60 * 1000

SELECT *

FROM [Table]

WHERE AUDITTIMESTAMP < @AUDITTIMESTAMP

Far away is close at hand in the images of elsewhere.
Anon.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 7

Hi Iostdba I am getting an error when trying to run your code posted on 5/9/05. Testing your first code Gives dates ranging from 2004-06-21 to 2005-01-03 Are these the actual dates these records were created? I was under the impression that this data was for records about 30 days old. Can you create a new record and check the audittimestamp to see if it is generating Dates. You can convert your sample data into a date but the question is are they valid dates. BOL states that in T-Sql timestamps are not datetime data types.

Mike

IF Object_ID('Tempdb..#Test') >0

DROP TABLE #test

Create Table #test

( AUDITTIMESTAMP decimal )