November 17, 2011 at 4:17 am
is this the best way to concate the date and time field in terms of performance of the query?
create table #temp
(date1 datetime, date2 datetime)
insert into #temp
select '2011-10-01 00:00:00.000', '1900-01-01 13:31:20.000'
union all
select '2011-10-02 00:00:00.000', '1900-01-01 02:31:20.000'
union all
select '2011-10-03 00:00:00.000', '1900-01-01 23:31:20.000'
select * From #temp
set dateformat ymd
select convert(datetime, replace(convert(varchar, date1, 111),'/','-') + ' ' +
+ convert(varchar, date2, 8))
from #temp
November 17, 2011 at 4:34 am
dva2007 (11/17/2011)
is this the best way to concate the date and time field in terms of performance of the query?create table #temp
(date1 datetime, date2 datetime)
insert into #temp
select '2011-10-01 00:00:00.000', '1900-01-01 13:31:20.000'
union all
select '2011-10-02 00:00:00.000', '1900-01-01 02:31:20.000'
union all
select '2011-10-03 00:00:00.000', '1900-01-01 23:31:20.000'
select * From #temp
set dateformat ymd
select convert(datetime, replace(convert(varchar, date1, 111),'/','-') + ' ' +
+ convert(varchar, date2, 8))
from #temp
Test a few different versions, it's the best way to make a decision like that.
BEGIN TRAN
--Standard TestEnvironment of 1,000,000 rows of random-ish data
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,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME))) AS randomDate,
RAND(CHECKSUM(NEWID())) + CAST('1900' AS DATETIME) AS randomTime
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
PRINT '========== Your Way =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT CONVERT(DATETIME, REPLACE(CONVERT(VARCHAR, randomDate, 111), '/', '-') + ' ' + + CONVERT(VARCHAR, randomTime, 8))
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Alternative=========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT randomDate + randomTime
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
ROLLBACK
November 17, 2011 at 5:14 am
thats perfect way of testing the alternative code. thanks a lot.
November 17, 2011 at 7:33 am
It has been repeatedly shown that manipulating dates by converting back and forth between character data performs very poorly versus using the datetime functions. The following code will always produce a result that incorporates the date part of date1 and the time part of date2.
SELECT DateAdd(d, DateDiff(d, date2, date1), date2)
FROM #Temp
This has the added advantage that you don't have to worry about date formats.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 17, 2011 at 7:40 am
dva2007 (11/17/2011)
thats perfect way of testing the alternative code. thanks a lot.
No problem. What did you pick in the end?
drew.allen (11/17/2011)
It has been repeatedly shown that manipulating dates by converting back and forth between character data performs very poorly versus using the datetime functions. The following code will always produce a result that incorporates the date part of date1 and the time part of date2.
SELECT DateAdd(d, DateDiff(d, date2, date1), date2)
FROM #Temp
This has the added advantage that you don't have to worry about date formats.
Drew
As Drew points out, your conversion to character is the slowest method.
SET NOCOUNT ON
--Standard TestEnvironment of 1,000,000 rows of random-ish data
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,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME))) AS randomDate,
RAND(CHECKSUM(NEWID())) + CAST('1900' AS DATETIME) AS randomTime
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Used to take the "display" time away
DECLARE @Holder DATETIME
DECLARE @COUNTER INT = 1
WHILE 1=1
BEGIN
PRINT REPLICATE('=',80)
PRINT '========== RUN NUMBER ' + CONVERT(VARCHAR(2),@COUNTER) + ' =========='
PRINT REPLICATE('=',80)
PRINT REPLICATE(' ',80)
PRINT '========== BASELINE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Holder = randomDate
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Your Way =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Holder = CONVERT(DATETIME, REPLACE(CONVERT(VARCHAR, randomDate, 111), '/', '-') + ' ' + + CONVERT(VARCHAR, randomTime, 8))
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Alternative=========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Holder = randomDate + randomTime
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Alternative 2=========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Holder = DATEADD(SECOND, DATEDIFF(SECOND, 0, randomTime), randomDate)
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
PRINT '========== Alternative 3=========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @Holder = DATEADD(d, DATEDIFF(d, randomTime, randomDate), randomTime)
FROM #testEnvironment
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',80)
SET @COUNTER = @COUNTER + 1
IF @COUNTER = 10
BEGIN
BREAK
END
END
================================================================================
========== RUN NUMBER 1 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 145 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3515 ms, elapsed time = 3526 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 176 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 219 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 208 ms.
================================================================================
================================================================================
========== RUN NUMBER 2 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 141 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3547 ms, elapsed time = 3560 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 175 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 216 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 212 ms.
================================================================================
================================================================================
========== RUN NUMBER 3 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 147 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3500 ms, elapsed time = 3485 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 175 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 226 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 211 ms.
================================================================================
================================================================================
========== RUN NUMBER 4 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 145 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3515 ms, elapsed time = 3504 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 177 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 224 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 209 ms.
================================================================================
================================================================================
========== RUN NUMBER 5 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 145 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3469 ms, elapsed time = 3493 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 177 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 219 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 208 ms.
================================================================================
================================================================================
========== RUN NUMBER 6 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 143 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3484 ms, elapsed time = 3490 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 173 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 220 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 213 ms.
================================================================================
================================================================================
========== RUN NUMBER 7 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 144 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3546 ms, elapsed time = 3526 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 181 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 220 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 209 ms.
================================================================================
================================================================================
========== RUN NUMBER 8 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 144 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3516 ms, elapsed time = 3525 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 173 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 218 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 213 ms.
================================================================================
================================================================================
========== RUN NUMBER 9 ==========
================================================================================
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 143 ms.
================================================================================
========== Your Way ==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3516 ms, elapsed time = 3523 ms.
================================================================================
========== Alternative==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 176 ms.
================================================================================
========== Alternative 2==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 220 ms.
================================================================================
========== Alternative 3==========
Table '#testEnvironment____________________________________________________________________________________________________00000000003A'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 207 ms.
================================================================================
November 17, 2011 at 8:10 am
Cadavre (11/17/2011)
PRINT '========== Alternative=========='
SELECT @Holder = randomDate + randomTime
FROM #testEnvironment
PRINT '========== Alternative 2=========='
SELECT @Holder = DATEADD(SECOND, DATEDIFF(SECOND, 0, randomTime), randomDate)
FROM #testEnvironment
PRINT '========== Alternative 3=========='
SELECT @Holder = DATEADD(d, DATEDIFF(d, randomTime, randomDate), randomTime)
FROM #testEnvironment
These alternates are not equivalent. Specifically, Alternatives 1 and 2 require that your randomDate have a time of midnight and your randomTime have a date of '1900-01-01'. Alternative 3 has no such restrictions. It will work with any two datetime values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 17, 2011 at 8:22 am
drew.allen (11/17/2011)
Cadavre (11/17/2011)
PRINT '========== Alternative=========='
SELECT @Holder = randomDate + randomTime
FROM #testEnvironment
PRINT '========== Alternative 2=========='
SELECT @Holder = DATEADD(SECOND, DATEDIFF(SECOND, 0, randomTime), randomDate)
FROM #testEnvironment
PRINT '========== Alternative 3=========='
SELECT @Holder = DATEADD(d, DATEDIFF(d, randomTime, randomDate), randomTime)
FROM #testEnvironment
These alternates are not equivalent. Specifically, Alternatives 1 and 2 require that your randomDate have a time of midnight and your randomTime have a date of '1900-01-01'. Alternative 3 has no such restrictions. It will work with any two datetime values.
Drew
Yes, but the OP's data has a datetime that only stores the date and a datetime that only stores the time. So presumably that means that the datetime that only stores date will always be midnight and the datetime that only stores a time will always be 1900-01-01.
In previous employment, I had a third party app that stored the date in the same way and found that addition was the fastest method of combining.
--edit--
The most important difference, imo, is that both the OPs original query and Alternative 2 drop milliseconds.
November 17, 2011 at 9:58 am
Cadavre,
i used your solution without converting into date time. I needed the comments around the performance and few alternative ways. thanks for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply