If anyone wonders about performance on the posted solutions.
CREATE TABLE Testing
(
VHRGDT numeric(8, 0) NOT NULL,
VHRGTM numeric(6, 0) NOT NULL
)
INSERT INTO Testing
SELECT TOP 1000000
CONVERT( char(8), a.create_date, 112),
REPLACE( CONVERT( char(8), a.create_date, 114), ':', '')
FROM sys.all_objects a, sys.all_objects b;
GO
DECLARE @Dummy datetime;
--CHECKPOINT
--DBCC FREEPROCCACHE WITH NO_INFOMSGS;
--DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--PRINT '-- ============= Drew ==================='
--SET STATISTICS TIME ON;
--select @Dummy = cast(FORMAT(VHRGDT, '0000-00-00') + ' ' + FORMAT(VHRGTM, '00:00:00.000') as datetime)
--from Testing;
--SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= tripleAxe ==================='
SET STATISTICS TIME ON;
select @Dummy = DATEADD(SECOND, CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 1, 2)) * 60 * 60 + CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 3, 2)) * 60 + SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)), 6), 5, 2), CONVERT(DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113))
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= tripleAxe2 ==================='
SET STATISTICS TIME ON;
select @Dummy = CONVERT( DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('0'+CONVERT(VARCHAR(6), VHRGTM), 6), 3, 0, ':'), 6,0,':'), 108)
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Lynn ==================='
SET STATISTICS TIME ON;
select @Dummy = msdb.dbo.agent_datetime(VHRGDT,VHRGTM)
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Kristen ==================='
SET STATISTICS TIME ON;
select @Dummy = CONVERT(datetime, RTRIM(VHRGDT)) + (VHRGTM * 9 + VHRGTM % 10000 * 6 + VHRGTM % 100 * 10) / 216e4
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Kristen2 ==================='
SET STATISTICS TIME ON;
select @Dummy = DateTimeFromParts(VHRGDT / 10000, (VHRGDT % 10000) / 100, VHRGDT % 100 , VHRGTM / 10000, (VHRGTM / 100) % 100, VHRGTM % 100, 0)
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Ed ==================='
SET STATISTICS TIME ON;
select @Dummy = CONVERT(DATETIME,CONVERT(VARCHAR(8),VHRGDT,0) + CHAR(32) + STUFF(STUFF(STUFF(CONVERT(VARCHAR(8) ,10000000 + VHRGTM,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,''),112)
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Sean ==================='
SET STATISTICS TIME ON;
select @Dummy = CAST(CAST(CAST(STR(VHRGDT) AS DATE) AS CHAR(10)) + ' ' + STUFF(STUFF(RIGHT('0' + CAST(VHRGTM AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
from Testing;
SET STATISTICS TIME OFF;
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT '-- ============= Luis ==================='
SET STATISTICS TIME ON;
select @Dummy = CAST( CAST( VHRGDT AS char(9)) + STUFF( STUFF( RIGHT( VHRGTM + 1000000, 6), 5, 0, ':'), 3, 0, ':') AS datetime)
from Testing;
SET STATISTICS TIME OFF;
GO 5
DROP TABLE Testing
-- ============= tripleAxe ===================
SQL Server Execution Times:
CPU time = 1107 ms, elapsed time = 1270 ms.
CPU time = 1186 ms, elapsed time = 1261 ms.
CPU time = 1138 ms, elapsed time = 1258 ms.
CPU time = 1123 ms, elapsed time = 1282 ms.
CPU time = 1154 ms, elapsed time = 1309 ms.
-- ============= tripleAxe2 ===================
SQL Server Execution Times:
CPU time = 843 ms, elapsed time = 1022 ms.
CPU time = 858 ms, elapsed time = 919 ms.
CPU time = 890 ms, elapsed time = 1039 ms.
CPU time = 905 ms, elapsed time = 973 ms.
CPU time = 936 ms, elapsed time = 1132 ms.
-- ============= Lynn ===================
SQL Server Execution Times:
CPU time = 5959 ms, elapsed time = 6142 ms.
CPU time = 6022 ms, elapsed time = 6341 ms.
CPU time = 5928 ms, elapsed time = 6250 ms.
CPU time = 6037 ms, elapsed time = 6243 ms.
CPU time = 5928 ms, elapsed time = 6200 ms.
-- ============= Kristen ===================
SQL Server Execution Times:
CPU time = 842 ms, elapsed time = 916 ms.
CPU time = 858 ms, elapsed time = 920 ms.
CPU time = 842 ms, elapsed time = 911 ms.
CPU time = 827 ms, elapsed time = 892 ms.
CPU time = 811 ms, elapsed time = 891 ms.
-- ============= Kristen2 ===================
SQL Server Execution Times:
CPU time = 874 ms, elapsed time = 989 ms.
CPU time = 905 ms, elapsed time = 1014 ms.
CPU time = 952 ms, elapsed time = 1121 ms.
CPU time = 843 ms, elapsed time = 937 ms.
CPU time = 890 ms, elapsed time = 993 ms.
-- ============= Ed ===================
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 1025 ms.
CPU time = 905 ms, elapsed time = 977 ms.
CPU time = 920 ms, elapsed time = 983 ms.
CPU time = 858 ms, elapsed time = 993 ms.
CPU time = 982 ms, elapsed time = 1049 ms.
-- ============= Sean ===================
SQL Server Execution Times:
CPU time = 1872 ms, elapsed time = 1978 ms.
CPU time = 1919 ms, elapsed time = 1992 ms.
CPU time = 1966 ms, elapsed time = 2071 ms.
CPU time = 1872 ms, elapsed time = 1946 ms.
CPU time = 1904 ms, elapsed time = 1994 ms.
-- ============= Luis ===================
SQL Server Execution Times:
CPU time = 967 ms, elapsed time = 1079 ms.
CPU time = 858 ms, elapsed time = 927 ms.
CPU time = 858 ms, elapsed time = 943 ms.
CPU time = 811 ms, elapsed time = 919 ms.
CPU time = 843 ms, elapsed time = 976 ms.