Search code examples
t-sqlsql-server-2000

Most Performant Way to Convert DateTime to Int Format

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?

Solution

  • 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.