DECLARE	@date DATETIME

SET	@date = CURRENT_TIMESTAMP

SELECT  @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added

Date	                               Date_Added
2013-02-18 10:29:50.343	               2013-02-20 04:11:31.383

DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'

SELECT CAST(GETDATE() AS DATETIME2)
    ,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT)
        ,DATEADD(millisecond
            ,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME))
            ,CAST(GETDATE() AS DATETIME2))))

DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'

SELECT CAST(GETDATE() AS DATETIME2)
    ,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT)
        ,DATEADD(millisecond
            ,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME))
            ,CAST(GETDATE() AS DATETIME2))))

/**********************************************************************************************************************
 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
;

        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;

========== 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.

        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.

SELECT GETDATE()
    ,DATEADD(hh, ABS(LEFT('41:41:41.041',2)), GETDATE()+('00:'+RIGHT('41:41:41.041',9)))

2013-02-22 12:54:58.843   2013-02-24 06:36:39.883

        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(hour, CAST(LEFT(@TimeToAdd, 2) AS INT)
                            ,DATEADD(ms
                            ,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9))
                            ,SomeDateTime))
   FROM dbo.JBMTest;
    SET STATISTICS TIME OFF;

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

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 271 ms.
========== DATETIME2 Compatible ==========

 SQL Server Execution Times:
   CPU time = 280 ms,  elapsed time = 291 ms.