Query to get yesterdays data only for a specific time interval Here is my where condition which I use for daily data but I've got to change the date
everyday.

WHERE X.stage = 'Approval'
AND
X.T_ExitTime between '2015-08-06 06:00:00:000' and  '2015-08-06 17:00:00:000'
ORDER BY x.T_ExitTime asc


How can we make this to use the timeframe 0600-1700 for yesterday or for the same day without having to change it manually everyday and so that we can make this a Job and automate it

I'm using SQL Server 2008
DECLARE @yesterday SMALLDATETIME;
SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE())));

SELECT ...
WHERE X.stage = 'Approval'
AND X.T_ExitTime &gt;= DATEADD(HOUR, 6, @yesterday)
AND X.T_ExitTime &lt; DATEADD(HOUR, 17, @yesterday)
ORDER BY x.T_ExitTime;


Don't think you want BETWEEN for datetime ranges, unless you really want to include 5:00 PM and not 5:00:00.0000001 PM.


What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries