Historical Date

Question

Historical Date

PSB

SSChampion

Points: 13195
More actions
May 3, 2016 at 5:57 am

#326096

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Smendle Hall of Fame Points: 3969 More actions · Answer 1

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())

PSB SSChampion Points: 13195 More actions · Answer 2

PSB

SSChampion

Points: 13195

May 3, 2016 at 7:19 am

#1876221

I have to run this code once every hour .

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

If server is 5 hour ahead of CST, but you're still taking the time from the server. Why would you need to change the time? What time defines "today" and "yesterday"?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

PSB SSChampion Points: 13195 More actions · Answer 4

I want the CST time recorded . If I don't do a -5 then May3rd, 7pm would be inserted as May 4th,00 am .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

PSB (5/3/2016)
I want the CST time recorded . If I don't do a -5 then May3rd, 7pm would be inserted as May 4th,00 am .

If the server is running in CST, then May3rd, 7pm will be May3rd, 7pm.

Is the server in a different time zone?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

PSB (5/3/2016)
I want the CST time recorded . If I don't do a -5 then May3rd, 7pm would be inserted as May 4th,00 am .

Maybe I'm misunderstanding the situation and the sample DDL isn't representative, but no times are recorded at all. The table column is of the DATE datatype.

Is that as it's supposed to be?

Cheers!

PSB SSChampion Points: 13195 More actions · Answer 7

Server is UTC time zone .

I just need to delete today's records and insert new ones and keep yesterday and older records. Rest (time zone) is not mandatory.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

This is a quick test on the date you would be using when run at different times. Are the values correct? Other than the formula, your code doesn't seem to be having a problem.

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

cteTally(n) AS(

SELECT TOP 48 DATEADD( hh, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1, '20160503') n

FROM E2

)

SELECT n, DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(hour,-5,n)))

FROM cteTally;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Smendle Hall of Fame Points: 3969 More actions · Answer 9

PSB (5/3/2016)
Server is UTC time zone .
I just need to delete today's records and insert new ones and keep yesterday and older records. Rest (time zone) is not mandatory.

DELETE FROM dbo.Temp1 WHERE HistoryDate = @HistoryDate and Day(@HistoryDate)=Day(GETDATE())

Aside from all the logical nightmares you are undoubtedly going through this will ensure that the historydate is the same date as today before deleting any records, which is really only effective for the 5 hour window from midnight to 5am that your -5 code does not account for.

drew.allen SSC Guru Points: 76997 More actions · Answer 10

PSB (5/3/2016)
Server is UTC time zone .
I just need to delete today's records and insert new ones and keep yesterday and older records. Rest (time zone) is not mandatory.

If the server is UTC time zone, maybe you should be using GETUTCDATE() instead of GETDATE().

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110208 More actions · Answer 11

DECLARE @HistoryDate DATETIME

SET @HistoryDate = dateadd(hour,-5, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate() )) )

_____________
Code for TallyGenerator