Grouping Query - 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 2000 Forums

Transact-SQL (2000)

Grouping Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-01-09 : 22:52:08

Hi there

I am trying to do grouping by ArchivedData which data type is datime with a full DDMMYY HH:MM:XX.XXX

I want to group that by actually DDMMYY and it does seem to work. I know with that smalldatime is actually converting to DDMMYY HH:MM. I even try to use the char(10) it works but not sort very well.

Any ideas?

quote:
select convert(smalldatetime, ArchivedDate, 103) , COUNT(ArchivedDate) from saveset
WHERE ArchivedDate > '2006-12-01'
GROUP BY convert(smalldatetime, ArchivedDate, 103)
ORDER BY convert(smalldatetime, ArchivedDate, 103) DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-09 : 22:55:45

[code]
select ArcDate = dateadd(day, datediff(day, 0, ArchivedDate), 0), COUNT(ArchivedDate)
from saveset
WHERE ArchivedDate > '2006-12-01'
GROUP BY dateadd(day, datediff(day, 0, ArchivedDate), 0)
ORDER BY ArcDate DESC
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 01:03:03

Did you even read about CONVERT and style 103 in Books Online?
If you have used style 112 with varchar (not smalldatetime), it would have worked.

select convert(varchar, ArchivedDate, 112) , COUNT(ArchivedDate) from saveset
WHERE ArchivedDate >= '20061201'
GROUP BY convert(varchar, ArchivedDate, 112)
ORDER BY convert(varchar, ArchivedDate, 112) DESC

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 08:17:42

Peso -- he shouldn't be converting his datetimes to varchar's, he should be simply removing the time portion. ktan's solution (or using functions like these: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx ) is what you should do. You should not be converting anything to varchar and returning varchars instead of the correct datetime datatypes.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 08:38:00

I agree keeping the datatype is the very best option

But since he already had an almost working query, I was just pointing out that spending 10 seconds more in Books Online, would help him the first place by just changing 103 style to 112 style.

I recon if he doesn't even grasp that, what would he do with khtans suggestion?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 08:58:14

true, the biggest downfall with the dateadd(datediff()) formula is that it is hard to read if you don't know the purpose; that's why I prefer a simple UDF library that does all this. May be a performance impact in using a UDF (though if they are well-implemented and compiled, there shouldn't be) but it is worth it for clear, short, easy to read and maintain code.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources