Luis Cazares - Thursday, May 25, 2017 7:27 AM

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 SCHEMABINDING
AS
RETURN
WITH 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 RoundedDatetime
FROM Dateparts
WHERE part = @DatePart;

 Here's the performance test .

DROP TABLE [dbo].[SampleTable]
GO
CREATE TABLE [dbo].[SampleTable](
    [RandomDate] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO SampleTable
SELECT TOP 100000 DATEADD( ms, CHECKSUM(NEWID()), GETDATE())
FROM sys.all_columns, sys.all_columns x;
GO
SELECT *
INTO #Args
FROM ( 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 = RandomDate
FROM SampleTable
CROSS JOIN #Args;

SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());
SELECT @Sysdatetime = SYSDATETIME();

SELECT @Datetime = [dbo].[fn_TruncateOrRoundDatetime] (RandomDate ,part ,chr)
FROM SampleTable
CROSS JOIN #Args;

SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());
SELECT @Sysdatetime = SYSDATETIME();

SELECT @Datetime = RoundedDatetime
FROM SampleTable
CROSS JOIN #Args
CROSS APPLY [dbo].[ifn_TruncateOrRoundDatetime] (RandomDate ,part ,n);

SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());

GO
DROP TABLE #Args;