Most Performant Way to Convert DateTime to Int Format

Question

I need to convert Datetime fields to a specifically formatted INT type. For example, I want 2000-01-01 00:00:00.000 to convert to 20010101.

What is the most performant way to make that conversion for comparison in a query?

Something like:

DATEPART(year, orderdate) * 10000 + DATEPART(month, orderdate) * 100 + 
    DATEPART(day, orderdate)

or

cast(convert(char(8), orderdate, 112) as int) 

What's the most performant way to do this?

Answer 1

Is this what you need

SELECT REPLACE(CONVERT(VARCHAR(10),'2010-01-01 00:00:00.000',101),'-','')

Answer 2

Your example of cast(convert(char(8), orderdate, 112) as int) seems fine to me. It quickly gets the date down to the format you need and converted to an int.

From an execution plan standpoint, there seems to be no difference between the two.

Answer 3

When you pass '2010-01-01 00:00:00.000' directly in your code, the SELECT statement looks at it as a string and not a datetime data type. Its not the same as selecting a datetime field directly.

There is no need to do outer CAST because SQL Server will do implicit conversion, here is a proof.

DECLARE @t DATETIME = '2010-01-10 00:00:00.000',@u INT
SELECT @u = CONVERT(CHAR(8), @t, 112)

IF ISNUMERIC(@u) = 1
PRINT 'Integer'