March 14, 2010 at 7:22 am
Hi
I have a job , this job transfers data from a server to another server(a table from a bank from a server to another table from another bank from another server)it works correctly and every day does it , now I want to add something to it , I want this job delete all the data which is for yesterday, but the problem is that I have a field which name is "RcvDate" and the data in it is like that "3/14/2010 3:24:00 PM"Now how can I divide date and after that what should I do for deleting?
and this is my sp:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[TransferToAnotherT_Source_News ]
as
begin
declare @LastID int
declare @maxid int
select @LastID=T_Source_News_ID from LastID_10135to1015
set @maxid=(select top 1 a.ID from SitesSQL.NEWSROOM.dbo.T_Source_News as a order by id desc)
set @LastID=@LastID+1
while @LastID <= @maxid
begin
INSERT INTO portal_storage.NewsRoom_Inside.dbo.T_Source_News(ID,Title ,Body,RcvDate,Keywords,Level2,Level3,Resource,Reporter,CatID,NewsScrtyID,NewsTypeID,SendFrom,PrtyID,Ersal,ED,EB,EC,DosID,EP,PJ,EJ,Script)
select ID,Title ,Body,RcvDate,Keywords,Level2,Level3,Resource,Reporter,CatID,NewsScrtyID,NewsTypeID,SendFrom,PrtyID,Ersal,ED,EB,EC,DosID,EP,PJ,EJ,Script
from SitesSQL.NEWSROOM.dbo.T_Source_News as a
where ID = @LastID
set @LastID=@LastID+1
set @maxid=(select top 1 a.ID from SitesSQL.NEWSROOM.dbo.T_Source_News as a order by id desc)
end
set @lastid=(select top 1 ID from SitesSQL.NEWSROOM.dbo.T_Source_News order by id desc)
update LastID_10135to1015 set T_Source_News_ID=@LastID
end
March 14, 2010 at 10:15 am
To alter a date so you have no time elements this will work
declare @date Char(10)
set @date = convert(char(10),getdate(),101)
To keep it as DATETIME with zero time values use this:
@midnight DATETIME
SELECT @midnight = DATEADD(dd,0,DATEDIFF(dd,0,getdate()))
SELECT @midnight
To learn more about DATETIME look at this and add these to your tool box:
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
With these you should be able to alter your SP to delete what you want it to delete. If not post back with additional questions.
I want this job delete all the data which is for yesterday
If this table, from which you want to delete the data, is a staging table why not just truncate the table or does the incoming data have both yesterday's date and today's date in the column RcvDate and thus you need to do what you want to do?
March 15, 2010 at 4:29 am
You shouldn't need to do anything as complicated as splitting/dividing your date field, as long as it is of the datetime data type.
A simple delete staement like the following will suffice.
DELETE
SomeTable
WHERE
SomeDateField >= DATEADD(dd,-1,DATEDIFF(dd,0,getdate()))
AND
SomeDateField < DATEADD(dd,0,DATEDIFF(dd,0,getdate()))
As always, test this fully before using in a production environment.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply