Most Performant Way to Convert DateTime to Int Format
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
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.