CODEWITHSUNDEEP
sqlsql-serversql-date-functions
Shanky
Shanky

Reputation: 626

How to get data for just two hour from previous day,

I need to get data from previous day (sql server db) for transferring to other DB (postgress), but since data is huge I want to transfer records for just 2 hr, I mean I will run this job 12 times a day and each time it will transfer rec for 2 hr, record transfered should not be duplicate.

So basically I need a query which I can schedule to run 12 times and which will transfer records for two hours each.

Upvotes: 1

Views: 2185

Answers (2)

Metaphor
Metaphor

Reputation: 6405

declare @StartHour datetime, @EndHour datetime

set @EndHour = dateadd(hh,datediff(hh,0,GetDate()),0)
set @StartHour = dateadd(hh,-2,@EndHour)

--The above make the query work with start and end on-the-hour
--so it can be run any time within one hour to get the data
--for the two hours ending on the previous hour

select * from whatever where TheDate between @StartHour and @EndHour

Upvotes: 1

Jay Zelos
Jay Zelos

Reputation: 1399

If you are timestamping inserts, then it should be simple to run a select to pull out only the preceding two hours worth of records. 

SELECT * FROM tblFoo WHERE tblFoo.insertionDate>DATEADD(hour,-2,GETDATE())

(If you want to be exact, then don't use GETDATE but hold the last date that ran in a table or variable somewhere and add two hours to it each time then setting it after running the query)

Upvotes: 0

Related Questions