Group by Date but has times included

[pdbowling]

Hello all.
I am working on a stored procedure. This code gets me what I need but I am having a challenge grouping the way I want. I am using SQL Server 2005 and Management Studio.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[udp_tatReport] 
   @StartDate DateTime, 
   @EndDate DateTime,
   @SbID Char(3)
AS
BEGIN
   DECLARE @utcStartDate DateTime
   DECLARE @utcEndDate DateTime
   Set @utcStartDate = dbo.uf_LocalTimeToUTC(getdate() ,getutcdate(), @StartDate)
   Set @utcEndDate = dbo.uf_LocalTimeToUTC(getdate(), getutcdate(), @EndDate)

   SELECT ProcessDate_UTC,
      Avg(OTime_Sec) as OTime,
	  Avg(TotalTime_Sec-OTime_Sec) as FTime,
	  Avg(TotalTime_Sec) as TotalTime,
	  Avg(CCount)
   FROM BatchInfo
   WHERE (ProcessDate_UTC BETWEEN @utcStartDate and @utcEndDate) 
   and
   SBID = @SbID
   Group By ProcessDate_UTC
END
GO

First, the UTC date has a time with it so I need a way to group by just the date portion. How do I go about stripping the time protion? (or doing the appropriate correct action if this is incorrect) Also, you can see that I'm converting from local time to UTC time in the procedure to get my results. I am wondering how to go about showing the queried UTC data as local time to the user.

Thanks, everyone.
P

Gather or post content for free.

http://www.ufo-articles.com

 

[gmmastros]

Maybe this...

   SELECT DateAdd(Day, DateDiff(Day, 0, ProcessDate_UTC), 0) As ProcessDate_UTC,
      Avg(OTime_Sec) as OTime,
      Avg(TotalTime_Sec-OTime_Sec) as FTime,
      Avg(TotalTime_Sec) as TotalTime,
      Avg(CCount)
   FROM BatchInfo
   WHERE (ProcessDate_UTC BETWEEN @utcStartDate and @utcEndDate)
   and
   SBID = @SbID
   Group By DateAdd(Day, DateDiff(Day, 0, ProcessDate_UTC), 0)

If this works for you, and you would like me to explain it, just ask.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom