dwain.c (10/13/2013)
Not that it means much until you upgrade, but you may want to consider avoiding these 2 because they seem to fail in SQL 2012 using Cadavre's test harness:
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + CAST('04:00:00' AS TIME)
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
The message I'm getting is:
Msg 402, Level 16, State 1, Line 40
The data types datetime and time are incompatible in the add operator.
Msg 402, Level 16, State 1, Line 53
The data types datetime and time are incompatible in the add operator.
That is very interesting, I'll have to remember to test it for confirmation when I get home to my 2012 instance.
dwain.c (10/13/2013)
Another option:
SELECT DATEADD(hour, 4, DATEDIFF(day, 0, GETDATE()))
I'm sure you already did it, but I was curious (I've also included "SELECT DATEADD(hour, 4,CAST(CAST(GETDATE() AS DATE) AS DATETIME))" as I've found that to be faster than the traditional DATEADD DATEDIFF methods): -
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('WARNING! May not work in SQL Server 2012',0,1) WITH NOWAIT;
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('WARNING! May not work in SQL Server 2012',0,1) WITH NOWAIT;
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;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime))
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) 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(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME))
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) 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:090
============
============
Loop: 0
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:517
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:293
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:357
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:230
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:253
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:240
============
Loop: 1
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:250
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:213
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:303
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:220
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:270
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:247
============
Loop: 2
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:250
============
WARNING! May not work in SQL Server 2012
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:227
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:250
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:227
============
Loop: 3
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:247
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:210
============
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:243
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:250
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:217
============
Loop: 4
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
WARNING! May not work in SQL Server 2012
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:317
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:240
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:283
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:397
============
Loop: 5
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:307
============
WARNING! May not work in SQL Server 2012
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:353
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:250
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:257
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:233
============