Convert integer to hex and hex to integer

Convert INT to hex:

SELECT CONVERT(VARBINARY(8), 16777215)

Convert hex to INT:

SELECT CONVERT(INT, 0xFFFFFF)

Update 2015-03-16

The above example has the limitation that it only works when the HEX value is given as an integer literal. For completeness, if the value to convert is a hexadecimal string (such as found in a varchar column) use:

-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

Note: The string must contain an even number of hex digits. An odd number of digits will yield an error.

More details can be found in the "Binary Styles" section of CAST and CONVERT (Transact-SQL). I believe SQL Server 2008 or later is required.

Actually, the built-in function is named master.dbo.fn_varbintohexstr.

So, for example:

SELECT 100, master.dbo.fn_varbintohexstr(100)

Gives you

100 0x00000064