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

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 2

You can try with TSQL builtin functions. It's not .NET tick compatible but it's still FAST sortable and you can pick your GRANULARITY on demand:

SELECT setup.DateToINT(GETDATE(),  4) -- will output 2019 for 2019-06-06 12:00.456 
SELECT setup.DateToINT(GETDATE(),  6) -- will output 201906 for 2019-06-06 12:00.456 
SELECT setup.DateToINT(GETDATE(), 20) -- will output 20190606120045660 for 2019-05-05 12:00.456     

CREATE FUNCTION setup.DateToINT(@datetime DATETIME, @length int) 
       RETURNS 
       BIGINT WITH SCHEMABINDING AS
BEGIN 
       RETURN CONVERT(BIGINT,
                      SUBSTRING(
                        REPLACE(REPLACE(
                        REPLACE(REPLACE(
                                CONVERT(CHAR(25), GETDATE(), 121)
                        ,'-','')
                        ,':','')
                        ,' ','')
                        ,'.','')
                    ,0
                    ,@length+1)
                    )
END
GO