Most Performant Way to Convert DateTime to Int Format

user_455292 0
Viewed 40670

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?

sql-server-2000 tsql
4 Answers

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
user_23771850
Related