Ah... ya got me between a rock and a hard spot here. 😉 I've dedicated a huge amount of time to dispelling SQL myths on the internet, especially when someone claims performance, so I'm right there with you when it comes to any claims of performance. I like to see people prove such statements.

However... not only has it been proven that conversion of dates to VARCHARs is comparatively dreadfully slow, but the method that Drew used is one of the faster methods there is and it will absolutely blow the doors off of any conversion to a VARCHAR using CONVERT. So I can understand why he's reluctant to spend any additional time on what has become well known previously proven fact.

That not withstanding, I'll be happy to prove it because it's a question asked so often that I got tired of writing ad hoc code for it and built a canned example. Drew's method is called DATEDIFF/DATETIME in the following code. Compare it to any of the conversions to VARCHAR and see why he was a bit adamant about it.

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

Purpose:

Create a voluminous test table with highly randomized DATETIME data.

--Jeff Moden

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

--===== Do this test in a nice safe place that everyone has.

USE TempDB

;

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

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

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

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

INTO dbo.JBMTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== This will take the display out of the picture so we

-- can measure the true processing time in memory.

DECLARE @BitBucketDATETIME DATETIME

PRINT '========== BASELINE =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = SomeDateTime

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== DATEADD/DATEDIFF 1 =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = DATEADD(d, 0, DATEDIFF(d, 0, SomeDateTime))

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== DATEADD/DATEDIFF 2 =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = DATEADD(dd,DATEDIFF(dd,0,SomeDateTime),0)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== DATEDIFF Implicit =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = DATEDIFF(d, 0, SomeDateTime)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== DATEDIFF/DATETIME =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CAST(DATEDIFF(d, 0, SomeDateTime) AS DATETIME)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== CONVERT =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CONVERT(DATETIME,CONVERT(VARCHAR,SomeDateTime,100))

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '========== CONVERT Implicit=========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CONVERT(VARCHAR,SomeDateTime,100)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== FLOOR ====='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CAST(FLOOR(CONVERT(FLOAT, SomeDateTime)) AS DATETIME)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== FLOOR Implicit ====='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = FLOOR(CONVERT(FLOAT, SomeDateTime))

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== ROUNDING 1 ====='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CAST(CAST(SomeDateTime - 0.50000004 AS INT) AS DATETIME)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== ROUNDING 1 Implicit ====='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CAST(SomeDateTime - 0.50000004 AS INT)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== ROUNDING 2 ======'

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = CAST(ROUND(CAST(SomeDateTime AS FLOAT),0,1) AS DATETIME)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

PRINT '===== ROUNDING 2 Implicit ======'

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = ROUND(CAST(SomeDateTime AS FLOAT),0,1)

FROM dbo.JBMTest WITH (NOLOCK)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',80)

For those that may have an interest, here's the output created by my 9 year old, single 1.8 GHz P4 CPU with 1GB of ram running Windows XP (sp3) and SQL Server 2005 (sp3).

(1000000 row(s) affected)

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 640 ms, elapsed time = 668 ms.

================================================================================

========== DATEADD/DATEDIFF 1 ==========

SQL Server Execution Times:

CPU time = 984 ms, elapsed time = 1026 ms.

================================================================================

========== DATEADD/DATEDIFF 2 ==========

SQL Server Execution Times:

CPU time = 797 ms, elapsed time = 810 ms.

================================================================================

========== DATEDIFF Implicit ==========

SQL Server Execution Times:

CPU time = 719 ms, elapsed time = 715 ms.

================================================================================

========== DATEDIFF/DATETIME ==========

SQL Server Execution Times:

CPU time = 734 ms, elapsed time = 737 ms.

================================================================================

========== CONVERT ==========

SQL Server Execution Times:

CPU time = 5500 ms, elapsed time = 5515 ms.

================================================================================

========== CONVERT Implicit==========

SQL Server Execution Times:

CPU time = 5375 ms, elapsed time = 5389 ms.

================================================================================

===== FLOOR =====

SQL Server Execution Times:

CPU time = 1016 ms, elapsed time = 1018 ms.

================================================================================

===== FLOOR Implicit =====

SQL Server Execution Times:

CPU time = 969 ms, elapsed time = 970 ms.

================================================================================

===== ROUNDING 1 =====

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 720 ms.

================================================================================

===== ROUNDING 1 Implicit =====

SQL Server Execution Times:

CPU time = 719 ms, elapsed time = 718 ms.

================================================================================

===== ROUNDING 2 ======

SQL Server Execution Times:

CPU time = 1453 ms, elapsed time = 1465 ms.

================================================================================

===== ROUNDING 2 Implicit ======

SQL Server Execution Times:

CPU time = 1532 ms, elapsed time = 1566 ms.

================================================================================