SQL Query Group By Datetime problem?

I have this SQL query:

  SELECT DISTINCT 
         [BatchCode]
         ,SUM([Quantity]) as 'Created'
         ,[TotalQuantity]
         ,[Status]
         ,[Destination]
 开发者_运维知识库        ,[DateCreated]
         ,[CreatedBy]
    FROM [FGIS].[dbo].[DropshipPackinglist]
GROUP BY BatchCode, TotalQuantity, Status, Destination, CreatedBy, ModifiedBy, DateCreated

The Result is this:

BatchCode               Created   TotalQuantity   Status     Destination        DateCreated               CreatedBy
---------------------------------------------------------------------------------------------------------------
0005041007100AHWA11HG   86        86              CREATED    MediTelecom S.A.   2010-09-10  00:00:00.000    NULL
0005041007100AHWA11HGK  19        50              ALLOCATED  USA                2010-09-12 07:35:17.000     jy
0005041007100AHWA11HGK  31        50              ALLOCATED  USA                2010-09-12 07:35:20.000     jy

---

My Problem now is I can't Group DateCreated because of it has different time .

I want to group it by date only. Example: 2010-09-12

Thanks and regards...

---

Use CAST or CONVERT to alter the DATETIME format so the time portion is omitted:

  SELECT [BatchCode],
         SUM([Quantity]) as 'Created',
         [TotalQuantity],
         [Status],
         [Destination],
         CONVERT(VARCHAR(10), [DateCreated], 101) AS datecreated,
         [CreatedBy]
    FROM [FGIS].[dbo].[DropshipPackinglist]
GROUP BY BatchCode, 
         TotalQuantity, 
         Status, 
         Destination, 
         CreatedBy, 
         ModifiedBy, 
         CONVERT(VARCHAR(10), [DateCreated], 101)

---

I guess it's worth posting this separately:

Using char conversions to chop the time off dates (cast or convert to varchar) is slower than using DateAdd(Day, DateDiff(Day, 0, DateCreated), 0). I worked up full script and performance testing results to support this assertion.

SELECT DISTINCT 
   BatchCode
   ,SUM(Quantity) as Created
   ,TotalQuantity
   ,Status
   ,Destination
   ,DateAdd(Day, DateDiff(Day, 0, DateCreated), 0) as DayCreated
   ,CreatedBy
FROM FGIS.dbo.DropshipPackinglist
GROUP BY
   BatchCode,
   TotalQuantity,
   Status,
   Destination,
   CreatedBy,
   ModifiedBy,
   DateDiff(Day, 0, DateCreated) -- note that the DateAdd convert back to datetime is not needed

Also, please note that your GROUP BY list is not the same as your SELECT list so some tweaking is needed.

UPDATE

It seems that the CPU savings for using DateAdd vs. varchar conversions, while a lot relatively, isn't a lot absolutely (just fractions of a millisecond per row). However, it is still a performance difference, and it seems best to me to save every bit possible.