Removing rows from a table - 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 2005 Forums

Transact-SQL (2005)

Removing rows from a table

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-08-05 : 10:19:16

I need to remove all rows from a table based on the date value being at lease sixty days old from the update date (i.e. today). The syntax needs to be part of a table update job.

What's the syntax to tell it to remove x number of rows. Does it start with 'Update Table' or 'Alter Table Alter column', etc.

I'm not really sure.

thx,

John

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-05 : 10:28:14

[code]
delete t
from table t
where update_date <= dateadd(day, -60, getdate())
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:18:48

quote:
Originally posted by latingntlman

I need to remove all rows from a table based on the date value being at lease sixty days old from the update date (i.e. today). The syntax needs to be part of a table update job.

What's the syntax to tell it to remove x number of rows. Does it start with 'Update Table' or 'Alter Table Alter column', etc.

I'm not really sure.

thx,

John

if your requirement is to delete a certain number of rows then use
DELETE TOP (x)
FROM Table
WHERE update_date <= dateadd(day, -60, getdate())

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-08-05 : 14:19:45

Thanks both, however now I need to display getdate() as 2008-08-06 00:00:00 since getdate is displaying as Aug 5 2008 2:17PM. This may cause some records to be/not be included.

update_date is formatted as Julian convert(smalldatetime,convert(varchar(10),dbo.convjulian2(sdtrdj),101))

Regards,

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 14:20:59

quote:
Originally posted by latingntlman

Thanks both, however now I need to display getdate() as 2008-08-06 00:00:00 since getdate is displaying as Aug 5 2008 2:17PM. This may cause some records to be/not be included.

update_date is formatted as Julian convert(smalldatetime,convert(varchar(10),dbo.convjulian2(sdtrdj),101))

Regards,

John

or use

dateadd(d,datediff(d,0,dbo.convjulian2(sdtrdj)),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources