GROUP BY date but not time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GROUP BY date but not time

Author

Topic

McBeef
Starting Member

14 Posts

Posted - 2011-06-23 : 14:22:50

I am trying to use GROUP BY on a table workdone to obtain sums on the number of transactions for serial number / date / work combinations. The original table contains serial (nvarchar), work (nvarchar), and datestamp (smalldatetime) columns. The datestamp includes hours and minutes and I would like to keep recording that info in case it might be useful later. But for the query, I want to only GROUP BY the date and ignore the time, a query that includes the time returns more results and these extra results are redundant day combinations. I also want the query results to retain smalldatetime format so that I can easily query for date ranges using BETWEEN.

I was able to come up with a solution but it is very rube goldberg and it seems like there is room for improvement. My answer requires turning the datestamp into nvarchar and then back into smalldatetime and also requires 2 queries. I am wondering if there is a more efficient solution.

Here is the solution I came up with:
I wrote a qryWorkDone that strips the time out of the date:

SELECT SERIALNUMBER, WORK, CONVERT(smalldatetime, CONVERT(nvarchar, DATESTAMP, 101)) AS DATESTAMP
FROM dbo.WORKDONE

Then made another query that groups the values:
SELECT SERIALNUMBER, WORK, DATESTAMP, COUNT(*) AS QTY
FROM dbo.qryWorkDone
GROUP BY SERIALNUMBER, WORK, DATESTAMP

Just being able to strip the time out of the smalldatetime with only 1 CONVERT rather than 2 would be a big improvement. Also GROUP BY only appears to work on actual values in the table or query, but not values obtained on the fly so this would seem to necessitate 2 queries, but I'm still learning so I could be wrong.

Suggestions?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-23 : 14:40:22

How about no converts?
select dateadd(day,datediff(day,0,getdate()),0)

And you can't group by DATESTAMP, but by dateadd(day,datediff(day,0,getdate()),0)

Jim

Everyday I learn something that somebody else already knew

shackclan
Starting Member

8 Posts

Posted - 2011-06-23 : 15:51:13

I always use the following:

SELECT CONVERT(VARCHAR, [date field], 101)

Thanks,
shackclan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 16:12:01

quote:
Originally posted by shackclan

I always use the following:

SELECT CONVERT(VARCHAR, [date field], 101)

Thanks,
shackclan

Why would you convert a nice typed value to a string (of unknown length at that)? You should always keep the proper data types, if possible. Converting dates to strings is probably the worst data-type abuse.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-23 : 16:49:15

If you only are going to group by the day, you can shorten it further

group by datediff(day, 0, datetimecolumnhere)

N 56°04'39.26"
E 12°55'05.63"

McBeef
Starting Member

14 Posts

Posted - 2011-06-23 : 18:50:53

working great, thank you everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources