Post reply

converting NULL

  • June 16, 2015 at 1:04 pm

    #301539

    i have a little confusion...

    DECLARE @name varchar(30)

    SET @name = NULL

    SELECT ISNULL(CONVERT(DATE,@name),'')

    Result: 1900-01-01

    But..

    DECLARE @name varchar(30)

    SET @name = NULL

    SELECT ISNULL(@name,'')

    Result: Blank Space

    Can someone please explain why i am getting this. Does NULL inherit a datatype after convert?

  • June 16, 2015 at 1:08 pm

    #1805857

    EDIT: Sorry I misunderstood the question.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • June 16, 2015 at 1:21 pm

    #1805866

    Look at it this way. You are converting VARCHAR(30) to a DATE. You happen to have a null value in this variable which still returns a DATE data type. The ISNULL returns the data type of the first argument, in this case a DATE. Since this is NULL it returns the empty string. Since ISNULL will return a DATE in this case SQL Server implicitly converts the empty string to the zero (0) date, 1900-01-01. If you run SELECT CONVERT(DATE,'') you will get 1900-01-01.

  • June 16, 2015 at 1:50 pm

    #1805878

    Thank you Lynn Pettis.

    can you explain this...

    DECLARE @name varchar(30)

    SET @name = NULL

    SELECT ISNULL(CONVERT(DATE,@name),'') as col1,CASE WHEN @name IS NULL THEN '' ELSE CONVERT(DATE,@name) END as col2, CONVERT(DATE,@name) as col3

    col1: 1900-01-01 (this makes sense form your previous explanation)

    col2: 1900-01-01 (should this be a blank space, since the first condition is satisfied?)

    col3: NULL (makes sense)

    Appreciate the response.

  • June 16, 2015 at 2:21 pm

    #1805887

    SolveSQL (6/16/2015)

    Appreciate the response.

    Basically, it is the same reason just not positional based. All values returned in the CASE statement must be of the same data type. In this case it wants to return a date data type. In this case, the empty string is converted to the zero date 1900-01-01. The query will fail if the value in the THEN clause cannot be converted to a date data type.

  • June 16, 2015 at 2:36 pm

    #1805896

    Thank you. Appreciate the quick response.

  • June 16, 2015 at 2:43 pm

    #1805898

    Lynn Pettis (6/16/2015)

    SolveSQL (6/16/2015)

    Appreciate the response.

    Basically, it is the same reason just not positional based. All values returned in the CASE statement must be of the same data type. In this case it wants to return a date data type. In this case, the empty string is converted to the zero date 1900-01-01. The query will fail if the value in the THEN clause cannot be converted to a date data type.

    Just to add a little bit on why SQL Server decided to return a date.

    As mentioned by Lynn, CASE statements will return a single data type. This data type is determined by the data type precedence. This same rule works for the COALESCE() function.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 17, 2015 at 7:53 am

    #1806076

    it just came full circle for me. Thank you SQL experts.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply