I need to find all issues register between 7PM and 7 AM everyday. I have datetime field but at midnght date will change so, i am not sure how to get this done.
SELECT
COUNT(calltime + 5/24) CALLS,
CAST(FLOOR(CAST((calltime + 5/24) AS float)) AS datetime) Theday
FROM MyTable
WHERE DATEPART(HH,calltime + 5/24) <= 12
GROUP BY CAST(FLOOR(CAST((calltime + 5/24) AS float)) AS datetime)
ORDER BY Theday
Just for clarity, this will group the number of calls from 7pm to 7am by the "second" date (the date the midnight to 7am calls were on).
Arial12
Member
29 Points
160 Posts
Date - find calls overlapping mid night
Nov 14, 2012 06:07 PM|LINK
Hi,
I need to find all issues register between 7PM and 7 AM everyday. I have datetime field but at midnght date will change so, i am not sure how to get this done.
can someone help please?
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Date - find calls overlapping mid night
Nov 14, 2012 06:23 PM|LINK
Something like this;
declare @theDate as Date
declare @startDateTime as DateTime
declare@stopDateTime as DateTime
set @theDate = somedate
set @startDateTime = DateAdd(hour, 19, @theDate)
set @endDateTime = DateAdd(hour, 12, @StartDateTime)
select etc
where yourDateField between @StartDateTime and @endDateTime
clevesteve
Participant
1405 Points
406 Posts
Re: Date - find calls overlapping mid night
Nov 14, 2012 06:41 PM|LINK
SELECT COUNT(calltime + 5/24) CALLS, CAST(FLOOR(CAST((calltime + 5/24) AS float)) AS datetime) Theday FROM MyTable WHERE DATEPART(HH,calltime + 5/24) <= 12 GROUP BY CAST(FLOOR(CAST((calltime + 5/24) AS float)) AS datetime) ORDER BY ThedayJust for clarity, this will group the number of calls from 7pm to 7am by the "second" date (the date the midnight to 7am calls were on).