Date grouping issue - Sql Server 2005

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

General SQL Server Forums

New to SQL Server Programming

Date grouping issue - Sql Server 2005

Author

Topic

dirtyeggs
Starting Member

4 Posts

Posted - 2012-12-03 : 17:03:52

Hello all - I am fairly new to sql and I am having the following issue.

I need to group some records based on date and I am trying to ignore the milliseconds - below is my query however, I see some results with the same timestamp but they are not grouped and I do not know why. I expected those two HAL records to be grouped. Any help would be appreciated. This is on SQL SERVER 2005.

SELECT count(*),ticker,CONVERT(varchar, CONVERT(VARCHAR(MAX), last_upd_date, 120), 120) from crx_order
group by last_upd_date,ticker
order by last_upd_date desc

results example
----------------
2 MSFT 2012-12-03 16:00:05
1 HAL 2012-12-03 14:59:52
1 HAL 2012-12-03 14:59:52

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-03 : 17:14:50

Because you're grouping by the actual value, not the truncated value. Try this:

SELECT count(*),ticker,DATEADD(ms, -DATEPART(ms,last_upd_date), last_upd_date) from crx_order
group by DATEADD(ms, -DATEPART(ms,last_upd_date), last_upd_date),ticker
order by DATEADD(ms, -DATEPART(ms,last_upd_date), last_upd_date) desc

dirtyeggs
Starting Member

4 Posts

Posted - 2012-12-04 : 09:27:17

I thought that might be the issue .... thanks it works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources