SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting by date - DateTrunc


Reporting by date - DateTrunc

Author
Message
mhtanner
mhtanner
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 184
Comments posted to this topic are about the item Reporting by date - DateTrunc
gsw8
gsw8
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 2
See the example below in which it appears the function does perform as expected:


mhtanner
mhtanner
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 184
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
gsw8
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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 unit
CREATE FUNCTION [dbo].[DateTrunc]
(
@Unit varchar(10),
@DateTime datetime,
@Num int
)
RETURNS DateTime
AS
BEGIN

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;

END

GO
mhtanner
mhtanner
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 184
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 tblJob
where 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search