Need to run a Select on a table for a specific time of day

Question

Post reply

Need to run a Select on a table for a specific time of day

itpcpro

Ten Centuries

Points: 1043
More actions
November 9, 2011 at 12:34 pm

#245922

I know this should be simple but I can not find or think of how to do it...
This is what I have and it works if I run the report at the specified time.. but what I want is to be able to run it anytime of the day...
What I need is to run this report at 7am to pull all jobs that ran from 5pm on the day before...
Select * from CAO_Job_History
Where Job_Status <> 'No History'
and Job_Name <> 'CAO_Job_History'
and Job_Date between DATEADD(hour, -14, CURRENT_TIMESTAMP) and GetDate()
Order by Job_Status,Job_Name
Thanks
Scott

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 1

I realize this statement has a lot of date functions, but it will return a datetime for the day before at 5 pm.

DATEADD(hh, 17, DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0))

itpcpro Ten Centuries Points: 1043 More actions · Answer 2

This does work if I run the job at 7am.. But what if I run the job at 10am or 1pm... I still only want the jobs that ran between 5pm and 7am...

Thanks

Scott

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 3

itpcpro (11/9/2011)
This does work if I run the job at 7am.. But what if I run the job at 10am or 1pm... I still only want the jobs that ran between 5pm and 7am...
Thanks
Scott

I apologize, I did not understand the complete requirement of wanting only the jobs between 5pm and 7am.

In terms of jobs running until 7am, is that exactly at 7 or the complete hour of "07:59:59.997"?

Try this:

Select * from CAO_Job_History

Where Job_Status <> 'No History'

and Job_Name <> 'CAO_Job_History'

and Job_Date BETWEEN

DATEADD(hh, 17, DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0)) ---- returns the day before with a time of 5:00 pm

AND DATEADD(hh, 7, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) ---- returns the current day with a time of 7:00 am

Order by Job_Status,Job_Name

itpcpro Ten Centuries Points: 1043 More actions · Answer 4

Thanks John, Yeah that would be jobs that run before 7am... So 6:59:59 would be the ending point and I have another job that would pickup at 7:00:00 and that would be in my Morning report.. So I will have 3 jobs in all Overnight, Morning and afternoon...

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 5

itpcpro (11/9/2011)
Thanks John, Yeah that would be jobs that run before 7am... So 6:59:59 would be the ending point and I have another job that would pickup at 7:00:00 and that would be in my Morning report.. So I will have 3 jobs in all Overnight, Morning and afternoon...

You will need to add another DATEADD(ms, -3, ....) function on the last one to return the 6:59:59.997 value. The minus 3 is due to the accuracy on the datetime datetype which rounds on increments of .000, .003, or .007 seconds.

-- notice the minus 3 milliseconds.

-- if you use a minus 1, there will be no conversion to just prior to 7am.

DATEADD(ms, -3, DATEADD(hh, 7, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)))

drew.allen SSC Guru Points: 76985 More actions · Answer 6

It's best NOT to use BETWEEN when using datetime ranges. You should use the following instead

WHERE YourDate >= StartDate

AND YourDate < EndDate

This will prevent you from double counting any jobs that fall exactly on the end date of one and the start date of another, e.g., exactly at 07:00AM in your scenario. It will also work regardless of the precision of the datetime variable. That is, it will work with smalldatetime, datetime, datetime2, and any other current or future date/time variables. Subtracting 3 ms from the end date will not work correctly with the current datetime2 datatype.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 7

Drew, you are correct. Thank you for posting the appropriate SQL. I had a brain lapse and know better. Best to perform a "less than" in most cases than mess around with time precision.