1

I am used to oracle and now been thrown T-SQL, I am doing a course shortly but can you help out until then.

I have a list that I need to group in minutes.

Sample of rowdate data

SELECT ROWDATE,count(rowdate)
FROM [mydb].[dbo].[mytable]
GROUP BY ROWDATE
order by 1

2010-08-16 15:01:18.110 1
2010-08-16 15:01:18.203 1
2010-08-16 15:01:18.377 1
2010-08-16 15:01:18.453 1
2010-08-16 15:01:18.530 1
2010-08-16 15:01:18.610 1
2010-08-16 15:01:18.703 1
2010-08-16 15:01:18.813 1
2010-08-16 15:01:18.953 1
2010-08-16 15:01:19.173 1
2010-08-16 15:01:19.360 1
2010-08-16 15:01:19.483 1
2010-08-16 15:01:19.593 1
2010-08-16 15:01:19.673 1
2010-08-16 15:01:19.733 1
2010-08-16 15:01:19.813 1
2010-08-16 15:01:19.890 1
2010-08-16 15:01:19.970 1
2010-08-16 15:01:20.047 1

I just want to group by mins. 

SELECT to_char(rowdate,'dd/MM/yyyy HH24:MI'),count(rowdate)
FROM mytable
GROUP BY to_char(rowdate,'dd/MM/yyyy HH24:MI')
order by 1

On sql server(T-SQL) what would be the equivalent script?

GSerg
  • 76,472
  • 17
  • 159
  • 346
icecurtain
  • 675
  • 3
  • 9
  • 30

4 Answers4

3

The TSQL equivalent of:

TO_CHAR(rowdate,'dd/MM/yyyy HH24:MI')

...is:

CONVERT(VARCHAR(16), rowdate, 20)

The expression "20" returns: yyyy-mm-dd hh:mi:ss(24h) - which is VARCHAR(19), so cutting that down to VARCHAR(16) omits the seconds.

DATETIME formatting is a real pain in TSQL - with SQL Server 2005, the only real way to get customizable formatting is to use a CLR function so in C# you could use the DateTime.ToString method...

Reference:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
3

That'd be

convert(varchar, rowdate, 100)

The format is not exactly the same, but the important thing, it's up to a minute, too.

EDIT:

Alternatively, you can avoid conversion to varchar at all:

group by dateadd(ms, -datepart(ms, rowdate) , dateadd(s, -datepart(s, rowdate), rowdate))
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • That gives time in h12:mi, not h24:mi – OMG Ponies Aug 16 '10 at 15:36
  • Correct. I thought it's not exactly relevant as you still can group by it and it's simplest thing I could come up with. There are other solutions, obviously. – GSerg Aug 16 '10 at 15:39
  • It's also risky to not define the VARCHAR length - usually, SQL Server will only allocate one character if a length is defined. But it's not an issue when I tested on SQL Server 2005 – OMG Ponies Aug 16 '10 at 15:43
  • No, the default lenght for varchar is `30` . – GSerg Aug 16 '10 at 15:50
  • `DECLARE @test VARCHAR SET @test = 'abc' SELECT @test` - you'll only get "a" – OMG Ponies Aug 16 '10 at 16:05
  • Eh, I explained it in the wrong way. It is documented that for `convert` function the length of the target data type is `30` by default. – GSerg Aug 16 '10 at 18:15
2

to floor the datetime down to the minute use (which is better than using string manipulations):

DATEADD(minute,DATEDIFF(minute,0,datetime),0)

so, to group by minutes, it would be:

SELECT 
    DATEADD(minute,DATEDIFF(minute,0,ROWDATE),0)
    ,count(rowdate)
FROM [mydb].[dbo].[mytable]
GROUP BY DATEADD(minute,DATEDIFF(minute,0,ROWDATE),0)
order by 1
KM.
  • 101,727
  • 34
  • 178
  • 212
  • You forgot the milliseconds part. – GSerg Aug 16 '10 at 15:52
  • @GSerg, I'm not sure what you are talking about? The way I floor the datetime to the minute is the generally accepted fasted method, see: [Floor a date in SQL server](http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server) – KM. Aug 16 '10 at 17:47
0

Is there a specific reason you want a conversion to (var)char?

datepart(minute, rowdate)

This returns just one number: the minute part of the datetime field. Very easy to group by.

Otherwise use CONVERT() or CAST() to convert between character types. You'll also want to make sure you use the right format. There's a little bit more about that here: http://www.mssqltips.com/tip.asp?tip=1145

Rob
  • 1,796
  • 14
  • 15
  • 1
    That will group together the same minute in different hours which isn't what the OP wants I think. I think `CONVERT(VARCHAR(20), GETDATE(), 100)` might do it. – Martin Smith Aug 16 '10 at 15:29
  • @Martin Smith: Almost, but "100" gives time in h12:mi, not h24:mi – OMG Ponies Aug 16 '10 at 15:38
  • @OMG - but as a grouping factor it should still work the same (it returns `Aug 16 2010 4:39PM` so won't group AM and PM together) – Martin Smith Aug 16 '10 at 15:39
  • Martin, perhaps, it was merely a suggestion. I don't know why my answer is so bad it deserves -1 tho. – Rob Aug 17 '10 at 07:31