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?

Sweater

Simple answer: test both and see which is "better" (e.g. quicker, uses less CPU, uses less reads/writes, etc) – Foxtail

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.

Maltz

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

Shallot

Answer 3

Is this what you need

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

Untitled

That's a lot more work than cast(convert(char(8), orderdate, 112) as int), which will work just as well. Your example doesn't even take converting to an INT into account. – Maltz

Answer 4

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'

Untitled

Maybe what you refer to is this SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME)) comes from here the conversion is implicit – Bogie