sql server 2008 r2 - Get yesterday's rows startign from 10AM

Question

For previous day I used use the below expression .

     DATE_INSERTED >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) 
     AND  DATE_INSERTED < DATEADD(day, DATEDIFF(day,0,GETDATE()),0)

How to get rows from yesterday 10Am to today 10AM

Answer 1

-- yesterday at midnight:
DECLARE @yesterday DATETIME = DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0);

SELECT
...
WHERE DATE_INSERTED >= DATEADD(HOUR, 10, @yesterday)  -- 10 AM yesterday
  AND DATE_INSERTED <  DATEADD(HOUR, 34, @yesterday); -- 10 AM today