Category: SQL Dev Tools

The section of SQL related 3DW Dev Tools

SQL Select records by the hour or the half hour

Here is a handy SQL snippet to select records by the half hour in a certain date range.

SELECT DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0) as LoginHour, Count(*) as NumberOfUsers FROM[my_db].[dbo].[MyTable] WHERE [LoginTime] BETWEEN '{0} 00:00:00.00' AND '{0} 23:59:59.999' GROUP BY DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0) ORDER BY DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0)

The result set will look something like this:

 

Here is a code to select by the hour if you don’t need to go down to the half hour

SELECT datepart(HH, [LoginTime]) as LoginHour, Count(*) as NumberOfUsers

FROM[my_db].[dbo].[MyTable]

WHERE[LoginTime] BETWEEN '09/07/2017 00:00:00.00' AND dateadd(dd, 1, '10/20/2017 23:59:59.999')

GROUP BY datepart(HH, [LoginTime])

ORDER BY datepart(HH, [LoginTime])

 

And the result set will resemble this:

 

Here is a snipped to get a all records regardless of the date, and count by the half hour.

SELECT substring(CONVERT(VARCHAR, DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0), 108),0,6) as LoginHour, Count(*) as NumberOfUsers FROM[my_db].[dbo].[MyTable]  GROUP BY substring(CONVERT(VARCHAR, DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0), 108),0,6) ORDER BY substring(CONVERT(VARCHAR, DATEADD(mi, DATEDIFF(mi,0,[LoginTime])/30*30,0), 108),0,6)