Date Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Function

Author

Topic

Confuddled
Starting Member

1 Post

Posted - 2004-12-06 : 11:59:35

Hi, Complete newbie here, struggling to accomplish what you guys would probably see as a simple task.

OK, I need to delete all transactions in a table older than a week. The problem is that the date is in milliseconds since 1970-01-01 00:00:00 GMT.
I can this to a date if I divide it by 1000 and use this:

select dateadd(ss, 1086708095.687, '1970-01-01')

Problem is, how do I do the following:

1/ Store today minus 7 days in this format in a script
2/ Compare this in a delete script so that I can remove everything older than this in the field logtime?

Sorry, as I said, just learning here

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-06 : 12:11:25

maybe:

declare @Date int
select @Date = 1086708095.687 - 60000*60*24*7 -- set the date for -7 days
delete MyTable
where logtime <= @Date

Go with the flow & have fun! Else fight the flow

AlDragon
Starting Member

12 Posts

Posted - 2004-12-06 : 22:12:36

--This brings back today - 7 days (getdate() - 7) with out any time
select convert(smalldatetime,convert(varchar(10),getdate() -7,121),121)

Declare @SevenDaysAgo datetime
SET @SevenDaysAgo = convert(smalldatetime,convert(varchar(10),getdate() -7,121),121)

--Select to see results to be deleted
Select *
from testdate
where datetest < @SevenDaysAgo -- use < or <= the seven days old

--Delete the records
Delete
from testdate
where datetest < @SevenDaysAgo -- use < or <= the seven days old

Al Franzini

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources