## Reporting by date - DateTrunc

 Author Message mhtanner Old Hand Group: General Forum Members Points: 373 Visits: 190 Comments posted to this topic are about the item Reporting by date - DateTrunc gsw8 Grasshopper Group: General Forum Members Points: 24 Visits: 2 See the example below in which it appears the function does perform as expected: mhtanner Old Hand Group: General Forum Members Points: 373 Visits: 190 I'm not sure what you'd expect the function to return when the number is not a divisor of the number of units in the implied containing period.For example, if we have an interval of months, we are typically looking at how they repeat within a year, and it makes sense to have n as 1,2,3,4 or 6. So, for example, 3 months = 1 quarter and we get 4 per year. But if we asked for a period of 5 months then the generated dates will not be aligned to a year and the results will not have an obvious meaning. Likewise we might be looking at minutes within an hour, so 1,2,3,4,5,6,10,12,15,20,30 make sense; for example to report an activity for every 5 minutes within the hour makes sense, but asking for the value in groups of 13 minutes would not as it wouldn't repeat each hour.In your example, 3 quarters (9 months) is not a divisor of a year, aka 4 quarters aka 12 months, so the result has no intuitive meaning.I should have added this as a conceptual limitation of the function. gsw8 Grasshopper Group: General Forum Members Points: 24 Visits: 2 I think this a serious limitation for a great function but why not do something like this:--where zero is the current value of the unitCREATE FUNCTION [dbo].[DateTrunc]( @Unit varchar(10), @DateTime datetime, @Num int)RETURNS DateTimeASBEGIN RETURN case left(@Unit, 1) when 'y' then dateadd(yy, datediff(yy, 0, @DateTime) -@num, 0) when 'q' then dateadd(qq, datediff(qq, 0, @DateTime) -@num, 0) when 'w' then dateadd(wk, datediff(wk, 0, @DateTime) -@num, 0) when 'd' then dateadd(dd, datediff(dd, 0, @DateTime) -@num, 0) when 'h' then dateadd(hh, datediff(hh, 0, @DateTime) -@num, 0) when 'n' then dateadd(mi, datediff(mi, 0, @DateTime) -@num, 0) when 's' then dateadd(ss, datediff(ss, dateadd(dd, datediff(dd, 0, @DateTime), 0), @DateTime) -@num, dateadd(dd, datediff(dd, 0, @DateTime), 0)) when 'm' then Case when @Unit in ('month', 'mm', 'm') then dateadd(mm, datediff(mm, 0, @DateTime) -@num, 0) when @Unit in ('minute', 'mi') then dateadd(mi, datediff(mi, 0, @DateTime) -@num, 0) else null end else null end;ENDGO mhtanner Old Hand Group: General Forum Members Points: 373 Visits: 190 Perhaps I'm misunderstanding how I should use the variant of the function you suggest.Here is an example of how I would expect my version to be used, in this case for multiples of minutes.Taking the 5th line as an example, if I wanted to have times truncated to 5 minute intervals I would use n = 5.`declare @dt as datetime; set @dt = '2017-12-20 13:17:44'select '1 min slot', dbo.datetrunc('mi', @dt, 1)select '2 min slot', dbo.datetrunc('mi', @dt, 2)select '3 min slot', dbo.datetrunc('mi', @dt, 3)select '4 min slot', dbo.datetrunc('mi', @dt, 4)select '5 min slot', dbo.datetrunc('mi', @dt, 5)select '6 min slot', dbo.datetrunc('mi', @dt, 6)select '10 min slot', dbo.datetrunc('mi', @dt, 10)select '15 min slot', dbo.datetrunc('mi', @dt, 15)select '20 min slot', dbo.datetrunc('mi', @dt, 20)`Which gives me (as the minutes portion) 1 -> 17, 2 -> 16, 3 -> 15, 4 -> 16, 5 -> 15, 6 -> 12, 10 ->10, 15 -> 15, 20 -> 0 - that is the portion truncated to multiples of n minutes.I can't see how I can get your flavour to do that.Here's another example. I want to see how many jobs have been created in each 5 minute interval for each day in some particular week`select dbo.datetrunc('d', CreatedOn, 5), dbo.datetrunc('mi', CreatedOn, 5), count(*) from tblJobwhere CreatedOn >='2017-02-01' and CreatedOn < '2017-02-08'group by dbo.datetrunc('d', CreatedOn, 5), dbo.datetrunc('mi', CreatedOn, 5) order by 1,2`This lists the day, the time truncated to 5 minutes, and the count.In my mind there is no meaning to a 7 or 23 minute truncation - if I wanted that then I'd use datepart and do a relevant division on that within the context of the date truncated to an hour or day.