## Function to Round or Truncate DateTime

 Author Message Shane Clarke Old Hand Group: General Forum Members Points: 327 Visits: 129 Comments posted to this topic are about the item Function to Round or Truncate DateTime Luis Cazares SSC Guru Group: General Forum Members Points: 107068 Visits: 21512 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 SSCertifiable Group: General Forum Members Points: 7422 Visits: 2296 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, '');*/` Shane Clarke Old Hand Group: General Forum Members Points: 327 Visits: 129 +xLuis Cazares - Thursday, May 25, 2017 7:27 AMWhat 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;`Only issue is when joining with other tables. That's why the original sample used Scalar Function. This would be great if you do as scalar. Thanks for your feedback . Luis Cazares SSC Guru Group: General Forum Members Points: 107068 Visits: 21512 +xShane Clarke - Tuesday, September 26, 2017 5:08 AMOnly issue is when joining with other tables. That's why the original sample used Scalar Function. This would be great if you do as scalar. Thanks for your feedback .Why would that be an issue? 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