ansaurus

Question

Answer 1

+3 A:

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)

OMG Ponies 2010-09-12 03:42:42

+1, That was my guess too :)

VoodooChild 2010-09-12 03:44:00

I prefer `CONVERT(VARCHAR(8), [DateCreated], 112)` because then you can sort by it as well as group by it. Of course, on SQL Server 2008 you can use `CONVERT(date, [DateCreated])` and not worry about the string conversion.

Gabe 2010-09-12 04:28:31

@Gabe: You're still sorting by VARCHAR using 112

OMG Ponies 2010-09-12 04:34:27

OMG Ponies: I was just saying that if you use `112` then you get a string where sorting in lexicographical order also sorts in chronological order. Sorting on the `101` converted string will not give you results in chronological order, so as a general rule `112` works better than `101`.

Gabe 2010-09-12 04:52:03

Using char conversions to chop the time off dates is **meaningfully** slower than using `DateAdd(Day, DateDiff(Day, 0, DateCreated), 0)`

Emtucifor 2010-09-12 05:31:14

Please see [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991) on my claims about conversion performance.

Emtucifor 2010-09-13 00:59:28

Answer 2

+1 A:

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.

Emtucifor 2010-09-12 05:33:54

I was going to make a similar post, but I don't have any timing information to back it up. I'd love to see your quantification of *meaningfully slower*.

Gabe 2010-09-12 06:43:47

@Gabe on Monday I'll try to post some info for you. What I found in past tests is that it takes a noticeable amount of CPU more to do string conversions rather than those involving math. This is a general programming truism that I've found consistent across all languages and platforms. If there are two ways to do something and one involves math and the other involves string conversion, the math way almost always wins. Sometimes there *are* efficiencies using strings when the math calculations are otherwise laborious, but for this straightforward difference the math wins.

Emtucifor 2010-09-12 16:51:38

@Gabe I updated my answer with a link, please see it.

Emtucifor 2010-09-13 00:58:44

So it looks like a string conversion takes about 2.5us while your method takes about 0.9us. In other words, your query has to have a million rows before it makes your query becomes *meaningfully* slower.

Gabe 2010-09-13 01:52:28

Thanks for putting that in real world terms, Gabe. Maybe "meaningfully" was a poor choice of words. In real-world terms, saving just a tiny bit per row may not matter that much. On the other hand, making the switch to DateDiff is easy and any performance gain we get does ease the load on the server. Could you post your numbers for the 2.5 microseconds and 0.9 microseconds calculations?

Emtucifor 2010-09-13 01:57:26

@Gabe Okay I see where you're getting it. I think subtracting the apparent base CPU cost makes sense, so I get about 1.8μs and 0.18μs.

Emtucifor 2010-09-13 07:34:40

ansaurus

tags:

views:

answers:

SQL Query Group By Datetime problem?

related questions