PSB (5/3/2016)
Hi,I need to wipe out just today's data and insert new ones . Yesterday's data should be untouched . But the procedure is wiping off yesterday's record using the code below . Server Time is 5 hours ahead of CST . That's why I'm using -5
CREATE TABLE dbo.Temp1
(
HistoryDate Date,
Task VARCHAR(100),
Count INT)
)
DECLARE @HistoryDate DATETIME = DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(hour,-5,getdate())))
DELETE FROM dbo.Temp1 WHERE HistoryDate = @HistoryDate
INSERT INTO dbo.Temp1 (
[Task]
,HistoryDate
,[Count]
)
SELECT
'ABCD 1',
@HistoryDate,
1 AS Count
Thanks,
PSB
You have a couple of options here.
1. NEVER run this code between the hours of 00:00:00 and 05:00:00 then your desired output will work as is.
However, I suspect you are probably running this throughout the day or have or will have that kind of requirement as your data grows in size, thus deleting records throughout the day. Which begs to question how are getting the old records......I digress Im sure you have that worked out already....back to the problem at hand....
2. If you must run this during the hours of midnight to 5am then this should work.
DELETE FROM dbo.Temp1 WHERE HistoryDate = @HistoryDate and Day(@HistoryDate)=Day(GETDATE())