I know it is irrelevant, as you're not going to be doing this over a lot of rows of data but in the interest of completeness, here's a performance comparison over 1 million rows: -
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
PRINT REPLICATE('=',80);
PRINT 'PERFORMANCE COMPARISON';
PRINT REPLICATE('=',80);
DECLARE @Loop CHAR(1) = '0', @HOLDER1 INT, @HOLDER2 DATETIME, @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER1 = COUNT(*)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('BaseLine Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
WHILE @Loop <= 5
BEGIN;
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(dd, DATEDIFF(dd, ''2000-01-01 00:00:00'', randomDateTime), ''2000-01-01 04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(DD,DATEDIFF(DD,0,randomDateTime),''04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
SET @Loop = @Loop + 1;
END;
GO
================================================================================
PERFORMANCE COMPARISON
================================================================================
BaseLine Duration: 00:00:00:083
============
============
Loop: 0
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:390
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:290
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:323
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:347
============
Loop: 1
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:230
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:353
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:257
============
Loop: 2
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:290
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:363
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:267
============
Loop: 3
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:330
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:220
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:327
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:250
============
Loop: 4
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:267
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:263
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:337
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:270
============
Loop: 5
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:260
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:243
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:320
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:357
============