Query to convert UTC to CST

I'm using SQL Server to count rows in a 24 hour period. I've accomplished that, however I can't figure out how to convert the UTC time to CST that the database stores.

Then...

How to breakdown the 24 hours into 24 x 1 hour blocks with a SUM or Count of each hours rows?

Convert UTC TIME to CST?

Query?

-- THEN

Select COUNT (*) AS Total From readmodels.Database Where Timestamp >= '2018-01-18' AND Timestamp <= '2018-01-19' 

-- Then Breakdown the count into 24 - 1 hour blocks

2 Answers

If you only need to convert from UTC to CST. You can simply use DATEADD(hour, -6, Timestamp) in your query.

e.g.

Select COUNT(*) as count, DATEPART(year, DATEADD(hour, -6, Timestamp)) as year, DATEPART(month, DATEADD(hour, -6, Timestamp)) as month, DATEPART(day, DATEADD(hour, -6, Timestamp)) as day, DATEPART(hour, DATEADD(hour, -6, Timestamp)) as hour From readmodels.Database Where DATEADD(hour, -6, Timestamp) >= '2018-01-18' AND DATEADD(hour, -6, Timestamp) <= '2018-01-19' Group by DATEPART(year, DATEADD(hour, -6, Timestamp)), DATEPART(month, DATEADD(hour, -6, Timestamp)), DATEPART(day, DATEADD(hour, -6, Timestamp)), DATEPART(hour, DATEADD(hour, -6, Timestamp)) 

--this is what I ended up using

SELECT dateadd(hour, datediff(hour, 0, TimeStamp), 0) as TimeStampHour_CST, Count(*) As Total_Per_Hour FROM readmodels.database WHERE Timestamp >= '2018-01-17' AND Timestamp <= '2018-01-18' GROUP BY dateadd(hour, datediff(hour, 0, TimeStamp), 0) ORDER BY dateadd(hour, datediff(hour, 0, TimeStamp), 0);