GROUP BY minute?

Is there a simpler way to group an SQL query by minute?

SELECT
datepart(year, DateTimeColumn)
datepart(month, DateTimeColumn)
datepart(day, DateTimeColumn)
datepart(hour, DateTimeColumn)
datepart(minute, DateTimeColumn)
FROM SomeTable GROUP BY
datepart(year, DateTimeColumn)
datepart(month, DateTimeColumn)
datepart(day, DateTimeColumn)
datepart(hour, DateTimeColumn)
datepart(minute, DateTimeColumn)

How could I get the result back as a datetime rather than 5 separate date related columns?SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
FROM YourTable
GROUP BY convert(varchar(16), DateTimeColumn, 120)

blindman|||This takes a minute and half to run, depending on the box

What about..

USE Northwind
GO
SET NOCOUNT ON

DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))

DECLARE @.i int
SELECT @.i = 0
WHILE @.i < 1000000
BEGIN
INSERT INTO @.myTable99 (Col3) SELECT 'x'
SELECT @.i = @.i + 1
END

SET NOCOUNT OFF

SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)|||Originally posted by Brett Kaiser
This takes a minute and half to run, depending on the box

What about..


That won't work. It will separate between different dates but not different hours; 3:31 and 4:31 will be grouped together. Try the following which does a few manual INSERTS.

USE Northwind
GO
SET NOCOUNT ON

DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))

INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')

SET NOCOUNT OFF

SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)|||Originally posted by blindman
SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
FROM YourTable
GROUP BY convert(varchar(16), DateTimeColumn, 120)

blindman

I had to add the cast in the group by section as well but then it works perfectly! Thanks!

USE Northwind
GO

DECLARE @.sampleTable TABLE (IdColumn int IDENTITY(1,1), DateColumn datetime DEFAULT GetDate(), DataColumn char(1))

INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')

SELECT COUNT(*), CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)
FROM @.sampleTable
GROUP BY CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)|||Good point...how about

USE Northwind
GO
SET NOCOUNT ON

DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))

DECLARE @.i int
SELECT @.i = 0
WHILE @.i < 1000000
BEGIN
INSERT INTO @.myTable99 (Col3) SELECT 'x'
SELECT @.i = @.i + 1
END

SET NOCOUNT OFF

SELECT SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2)

Returns:

---- ---- ----
2003-11-11 15 2 365972
2003-11-11 15 3 634028

(2 row(s) affected)