Reporting by date - DateTrunc
SQL Server has DateAdd and DateDiff functions, but lacks a DateTrunc function to truncate a date to some time interval (or multiple thereof). Using DateTrunc is a convenient way of e.g. listing sales by week or month.
Whilst the SQL fragment to do the truncation is simple, if subtle, I find I can never remember the syntax and always need to look it up.
The code supplied provides a simple and easy-to-remember function for doing this. An example of use might be as follows, to return a count of jobs by quarter:
select dbo.DateTrunc('q', JobDate, 1) as qtr, count(*) as cnt
from Job
group by dbo.DateTrunc('q', JobDate, 1)
order by 1
Whilst, as a function, it is slower than the equivalent code [dateadd(qq, datediff(qq, 0, JobDate) , 0) in this case] it is fine for ad-hoc use on all but the largest tables. And it provides a convenient place to store the definitions to save looking it up on the web each time even if you usually use the explicit variant.
The final parameter, @Num, is useful if, for example, you wanted the number of calls for every 5 minutes or 15 seconds or whatever.
It works and is tested in SQL Server 2005.
The stored procedure is based on code in the following article, https://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server, but with a tweak for the seconds calculation (where there is a danger of overflow).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mike Tanner
-- Create date: Nov 2017
-- Description:Truncates a given datetime to the year, quarter, month, week, day, hour, minute, second or a multiple thereof
--@Unit is one of the following:
--year, yy, yyyy, y
--quarter, qq, q
--month, mm, m
--week, wk, ww
--day, dd, d
--hour, hh, h
--minute, mi, n
--second, ss, s
--@TheDateTime is the datetime to be truncted
--@Num is the number of units
--
--Examples
--select dbo.DateTrunc('q', GetDate(), 1) gives the current quarter
--select dbo.DateTrunc('mi', GetDate(), 5) gives the time truncated to 5 minutes
-- =============================================
ALTER 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 * @num, 0)
when 'q' then dateadd(qq, datediff(qq, 0, @DateTime) / @num * @num, 0)
when 'w' then dateadd(wk, datediff(wk, 0, @DateTime) / @num * @num, 0)
when 'd' then dateadd(dd, datediff(dd, 0, @DateTime) / @num * @num, 0)
when 'h' then dateadd(hh, datediff(hh, 0, @DateTime) / @num * @num, 0)
when 'n' then dateadd(mi, datediff(mi, 0, @DateTime) / @num * @num, 0)
when 's' then dateadd(ss, datediff(ss, dateadd(dd, datediff(dd, 0, @DateTime), 0), @DateTime) / @num * @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 * @num, 0)
when @Unit in ('minute', 'mi')then dateadd(mi, datediff(mi, 0, @DateTime) / @num * @num, 0)
else null
end
else null
end;
END
GO 
