ERROR USING CASEWITH DATES IN A SELECT STATEMENT

Question

BEGIN

    DECLARE @a SMALLINT;

    SET @a = 1;

    SELECT ABS= CASE
                          WHEN @a = 1 THEN YEAR(GETDATE())
                         WHEN @a = 2 THEN GETDATE()
   END 

END

Why do I get the following result:

ABS
----
1905-07-07 00:00:00.000

When it Should be

ABS
----
2013

But, if I comment the line   "WHEN @a = 2 THEN GETDATE()", Then the execution result is OK.

Answer 1

Try below.

BEGIN
DECLARE @a SMALLINT;
SET @a = 1;
IF @a=1 
SELECT YEAR(GETDATE())
ELSE
SELECT GETDATE()
END

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

This is because the data type precedence specifies datetime is higher than INT... therefore what is happening is that the value 2013 is converted to DATETIME, as 1905-07-07 00:00:00.000..

SELECT CONVERT(datetime, 2013) returns the same value

---

Thanks! Josh Ash