## Function to Round or Truncate DateTime

 Author Message Shane Clarke SSC-Enthusiastic Group: General Forum Members Points: 154 Visits: 118 Comments posted to this topic are about the item Function to Round or Truncate DateTime Luis Cazares SSC Guru Group: General Forum Members Points: 65288 Visits: 20238 What about creating a function that would run 5 times faster?`CREATE FUNCTION ifn_TruncateOrRoundDatetime( @Datetime datetime, @DatePart char(2), @Truncate bit)RETURNS TABLE WITH SCHEMABINDINGASRETURNWITH Dateparts (part, RoundedDatetime)AS( SELECT 'SS', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'MI', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'HH', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'DD', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0) UNION ALL SELECT 'WK', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0) UNION ALL SELECT 'MM', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0) UNION ALL SELECT 'QQ', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0) UNION ALL SELECT 'YY', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0) UNION ALL SELECT 'S', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'N', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'H', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'D', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0) UNION ALL SELECT 'W', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0) UNION ALL SELECT 'M', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Q', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Y', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Second', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'Minute', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'Hour', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE())) UNION ALL SELECT 'Day', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Week', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Month', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Quarter', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0) UNION ALL SELECT 'Year', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0))SELECT RoundedDatetimeFROM Dateparts WHERE part = @DatePart;` Here's the performance test .`DROP TABLE [dbo].[SampleTable]GOCREATE TABLE [dbo].[SampleTable]( [RandomDate] [datetime] NULL) ON [PRIMARY]GOINSERT INTO SampleTableSELECT TOP 100000 DATEADD( ms, CHECKSUM(NEWID()), GETDATE())FROM sys.all_columns, sys.all_columns x;GOSELECT *INTO #ArgsFROM ( VALUES('ss'),('mi'),('hh'),('dd'),('mm'),('yy'))dateparts(part)CROSS JOIN ( VALUES('round',0), ('truncate',1)) RoundOrTruncate(chr,n)DECLARE @Datetime datetime, @Sysdatetime datetime2 = SYSDATETIME()SELECT @Datetime = RandomDateFROM SampleTableCROSS JOIN #Args;SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());SELECT @Sysdatetime = SYSDATETIME();SELECT @Datetime = [dbo].[fn_TruncateOrRoundDatetime] (RandomDate ,part ,chr)FROM SampleTableCROSS JOIN #Args;SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());SELECT @Sysdatetime = SYSDATETIME();SELECT @Datetime = RoundedDatetimeFROM SampleTableCROSS JOIN #ArgsCROSS APPLY [dbo].[ifn_TruncateOrRoundDatetime] (RandomDate ,part ,n);SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());GODROP TABLE #Args;` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 Bill Talada SSCarpal Tunnel Group: General Forum Members Points: 4644 Visits: 2224 Interesting, I took a very different approach when I wrote this similar function years ago.`CREATE FUNCTION dbo.DateTime2Precision (@dt datetime2(7), @PRECISION varchar(7)) returns datetime2AS-- written BY Bill TaladaBEGINDECLARE @filler varchar(27), @length int, @fixed datetime2SET @filler = 'cc00-01-01 00:00:00.0000000' --'ccyy-mm-dd hh:mm:ss.mmmmmmm'SET @length = CASE @PRECISION WHEN 'century' THEN 2 WHEN 'year' THEN 4 WHEN 'month' THEN 7 WHEN 'day' THEN 10 WHEN 'hour' THEN 13 WHEN 'minute' THEN 16 WHEN 'second' THEN 19 ELSE 0 ENDIF @length = 0 SET @fixed = @dtELSE SET @fixed = cast(LEFT(CONVERT(varchar(27), @dt, 121),@length) + RIGHT(@filler,27-@length) AS datetime2) RETURN @fixedENDgo/*DECLARE @dt datetime2;SET @dt = SYSDATETIME();SELECT dbo.DateTime2Precision(@dt, 'century'), dbo.DateTime2Precision(@dt, 'year'), dbo.DateTime2Precision(@dt, 'month'), dbo.DateTime2Precision(@dt, 'day'), dbo.DateTime2Precision(@dt, 'hour'), dbo.DateTime2Precision(@dt, 'minute'), dbo.DateTime2Precision(@dt, 'second'), dbo.DateTime2Precision(@dt, '');*/`