• 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;

    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