1

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

  • Why don't you make an automatic job? Cronjob? – oNare Aug 11 '15 at 21:38
  • @oNare Creating the job isn't the problem, it's dynamically determining "yesterday" in the query. – Aaron Bertrand Aug 11 '15 at 21:46
4
DECLARE @yesterday SMALLDATETIME;
SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE())));

SELECT ...
WHERE X.stage = 'Approval'
AND X.T_ExitTime >= DATEADD(HOUR, 6, @yesterday)
AND X.T_ExitTime < 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.

  • Thanks a lot this is what I was looking for.... I think I can manage with this....Thanks again – CrashOverride Aug 11 '15 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.