How to Specifying date range (Date + Hour) in the query

Database
Enthusiast

How to Specifying date range (Date + Hour) in the query

I am trying find out the CPU time utilization for the previous day by some intensive SQL jobs. The input is from the DBQL table. I would like to specify the time range in the previous day. How I do that. If I specify the value the query looks like this:

##############
select sum(TotalCpuTime) from DBC.QryLog WHERE LogonDateTime BETWEEN TIMESTAMP '2006-01-27 08:00:00.00' AND TIMESTAMP '2006-01-27 18:00:00.00' AND TotalCpuTime > 10000;
#############

In the above query I need to replace *** TIMESTAMP '2006-01-27 08:00:00.00' AND TIMESTAMP '2006-01-27 18:00:00.00'*** with DATE variable. What I am looking is something like

#########
select sum(TotalCpuTime) from DBC.QryLog WHERE LogonDateTime BETWEEN <> AND <> AND TotalCpuTime > 10000;
#########

Your help would be highly appreciated.

Thanks,

Krishna.R
1 REPLY
Enthusiast

Re: How to Specifying date range (Date + Hour) in the query

I am not sure if i understood your question correct.

But if you want the timestamps to be date instead you can cast them to date.

select sum(TotalCpuTime)
from DBC.QryLog
WHERE cast(LogonDateTime as date) BETWEEN cast(TIMESTAMP '2006-01-27 08:00:00.00' as date)
AND cast(TIMESTAMP '2006-01-27 18:00:00.00' as date)
AND TotalCpuTime > 10000;

is this what you are looking for