Yesterday I received an email from a friend asking how do SIGN function works. Well SIGN Function is very fundamental function. It will return the value 1, -1 or 0. If your value is negative it will return you negative -1 and if it is positive it will return you positive +1. Let us start with a simple small example.

DECLARE @IntVal1 INT, @IntVal2 INT,@IntVal3 INT
DECLARE @NumVal1 DECIMAL(4,2), @NumVal2 DECIMAL(4,2),@NumVal3 DECIMAL(4,2)
SET @IntVal1 = 9; SET @IntVal2 = -9; SET @IntVal3 = 0;
SET @NumVal1 = 9.0; SET @NumVal2 = -9.0; SET @NumVal3 = 0.0;
SELECT SIGN(@IntVal1) IntVal1,SIGN(@IntVal2) IntVal2,SIGN(@IntVal3) IntVal3
SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal2) NumVal3  

The above function will give us following result set.

You will notice that when there is positive value the function gives positive values and if the values are negative it will return you negative values. Also you will notice that if the data type is  INT the return value is INT and when the value passed to the function is Numeric the result also matches it. Not every datatype is compatible with this function.  Here is the quick look up of the return types.

• bigint -> bigint
• int/smallint/tinyint -> int
• money/smallmoney -> money
• numeric/decimal -> numeric/decimal
• everybody else -> float

USE tempdb
GO
CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-22 16:15', '2012-06-20 16:15'
UNION ALL
SELECT '2012-06-24 16:15', '2012-06-22 16:15'
UNION ALL
SELECT '2012-06-22 16:15', '2012-06-22 16:15'
GO
-- Using Case Statement
SELECT CASE
WHEN DATEDIFF(d,Date1,Date2) > 0 THEN 1
WHEN DATEDIFF(d,Date1,Date2) < 0 THEN -1
ELSE 0 END AS Col
FROM TestTable
GO
-- Using SIGN Function
SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO

This was interesting blog post for me to write. Let me know your opinion.

Reference: Pinal Dave (http://blog.sqlauthority.com)