CODEWITHSUNDEEP
sqlsql-serverdatetimetime
frank
frank

Reputation: 67

SQL Time function

Hi i am writing a SQL query for a report which will give the amounts of leads coming in for the day since 8am. The report is set to go out every our so for example the 8am report will show leads which came in from 7am-8am and the 9am report will show from 7am-9am the 10am from 7am-10am and so on, i would like to know if someone could provide me with the time function to put in the where cluase (something along the lines of:

T.LeadDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) -1)

thanks

Upvotes: 1

Views: 765

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15251

For completeness' sake, this will get you the count per day considering your rule:

-- Get counts of records per day
-- Anything prior to 8am is considered the prior day.
select cast(dateadd(hh, -8, MyDate) as date) FiscalDate, count(*) as Num
from MyTable
group by cast(dateadd(hh, -8, MyDate) as date)
order by cast(dateadd(hh, -8, MyDate) as date)

Upvotes: 0

Kevin
Kevin

Reputation: 7309

This will get you everything with a LeadDate after 7a.m. of the current day

select * from TABLENAME t where t.LeadDate >= dateadd(hh,7, DATEDIFF(dd, 0, GETDATE()))

You could also use the following, which seems a little clearer to me.

select * from TABLENAME t where t.LeadDate >= dateadd(hh,7, CONVERT(datetime,CONVERT(date, getdate())))

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

WHERE T.LeadDate >= DATEADD(hh,7,DATEDIFF(D, 0, GETDATE()))

There is no need to cast the DATEDIFF(D, 0, GETDATE()) part back to a time

Upvotes: 1

Related Questions