That works fine for DATETIME2 but my intent was to show how using simple date addition can make the code, well... simple. Not only does it make the code much more simple, but there's a performance advantage, as well, and I wish they had included date addition in the newer date related data types.

Of course, without a typical million row test, claims of performance just hearsay. So, without further ado, here's my standard million row test table for such tests...

/**********************************************************************************************************************

Purpose:

Create a voluminous test table with various types of highly randomized data.

--Jeff Moden

**********************************************************************************************************************/

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL

DROP TABLE dbo.JBMTest

;

--===== Create and populate a 1,000,000 row test table.

-- "SomeID" has a range of 1 to 1,000,000 unique numbers

-- "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers

-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times

-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"

-- "SomeName" contains random characters at random lengths from 2 to 20 characters

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))

+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),

SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding

SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),

SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),

SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)

INTO dbo.JBMTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

ALTER TABLE dbo.JBMTest

ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90

;

Here's the code being tested. Note that I did have to modify your code to work with DATETIME. The modifications were just to remove the CASTs to DATETIME2 that you did. Please check it to ensure that I faithfully kept the essence of the DATETIME2 compatible code.

RAISERROR('========== Simple date addition ==========',0,1) WITH NOWAIT;

DECLARE @TimeToAdd CHAR(12),

@Bitbucket DATETIME;

SELECT @TimeToAdd = '41:41:41.041';

SET STATISTICS TIME ON;

SELECT @Bitbucket = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)), SomeDateTime+('00:'+RIGHT(@TimeToAdd,9)))

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

GO

RAISERROR('========== DATETIME2 Compatible ==========',0,1) WITH NOWAIT;

DECLARE @TimeToAdd CHAR(12),

@Bitbucket DATETIME;

SELECT @TimeToAdd = '41:41:41.041';

SET STATISTICS TIME ON;

SELECT @Bitbucket = DATEADD(ms, 1, DATEADD(hour, CAST(LEFT(@TimeToAdd, 2) AS INT)

,DATEADD(ms

,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9))

,SomeDateTime)))

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

I'm sure that someone can write even faster code to demonstrate the performance advantage but here are the results I get on my older desktop box using SQL Server 2005 DE.

========== Simple date addition ==========

SQL Server Execution Times:

CPU time = 1156 ms, elapsed time = 1350 ms.

========== DATETIME2 Compatible ==========

SQL Server Execution Times:

CPU time = 1625 ms, elapsed time = 1799 ms.

Of course, since we're adding the same amount of time to all the rows, it's also much more efficient to preconvert the string to a DATETIME and then use some even simpler date addition.

RAISERROR('========== Preconverted date addition ==========',0,1) WITH NOWAIT;

DECLARE @TimeToAdd CHAR(12),

@Bitbucket DATETIME,

@TimeToAddDT DATETIME;

SELECT @TimeToAdd = '41:41:41.041';

SET STATISTICS TIME ON;

SELECT @TimeToAddDT = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)),('00:'+RIGHT(@TimeToAdd,9)))

SELECT @Bitbucket = SomeDateTime + @TimeToAddDT

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

GO

========== Preconverted date addition ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 844 ms, elapsed time = 1007 ms.