asked
SQL Server Datediff, results split into days
Is there a way to return the results of the SQL Server Datediff function split into days? For example, the beginning datetime could be Jan 1, 6 pm, and the ending could be Jan 4, 3 am. The desired result, by hours per day, should show: Jan 1, 6 hours; Jan 2, 24 hours; Jan 3, 24 hours; Jan 4, 3 hours.
Hi, You can use a tally table to do it. There are many versions on the web, here is a search on [SQL Server Central][1] for Tally tables. I personally prefer to use a UDF to generate them but you can pick what ever method suits your environment. Once you have a tally table the SQL below will break the differences out. A few points. 1. The UDF I use has a lower bound of 1, so I have to use N-1 in the SQL. 2. Minutes are ignored, so 0:59 to 1:01 counts as a full hour. DECLARE @date1 DATETIME = '20150101 18:00:00' , @date2 DATETIME = '20150104 03:00:00'; SELECT CAST(DATEADD(hour, n - 1, @date1) AS DATE) AS theday , COUNT(1) AS hoursinday FROM dbo.udf_tally_table(DATEDIFF(hh, @date1, @date2)) GROUP BY CAST(DATEADD(hh, n - 1, @date1) AS DATE) [1]:
http://www.sqlservercentral.com/search/?q=tally+table
15 People are following this question.
