I need some help in finding why the following `T-SQL` statement returns `1` (true):

    SELECT IIF( 0 = '', 1, 0)

I guess someone has change an `ANSI` option like `SET ANSI_NULLS` or something else that is causing the behavior.

My issue is that I am joining some values and in the final row set I have values which are joined by `0` and `''` values, which is not correct.
Top Answer
Tom V (imported from SE)
That is just documented behavior. I don't think anyone messed with the settings.

See [data type precedence](https://msdn.microsoft.com/en-us/library/ms190309.aspx) on MSDN.

> When an operator combines two expressions of different data types, the
> rules for data type precedence specify that the data type with the
> lower precedence is converted to the data type with the higher
> precedence.

The empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date.

I think your real problem is that your design is so that you have to join on fields of a different data type. The only way to get around this is to have a convert on your join clause which will hurt query performance. The main problem is probably with the schema design.

However illogical it may seem, converting an empty string to other data types produces arbitrary values.

This code:

    SELECT CONVERT(int, '')
    SELECT CONVERT(float, '')
    SELECT CONVERT(date, '')
    SELECT CONVERT(datetime, '')

Produces this output:

    0
    0
    1900-01-01
    1900-01-01 00:00:00.000

You could expect then that this behavior is consistent between other preceding datatypes and expect that converting 0 to a date would produce the same arbitrary value but it doesn't.

    SELECT CONVERT(date, 0)

Produces

> Explicit conversion from data type int to date is not allowed.

Because it's not a [supported conversion](https://msdn.microsoft.com/en-us/library/ms191530.aspx)

while

    SELECT CONVERT(datetime, 0)

Returns 

> January, 01 1900 00:00:00

So yes, it's weird and arbitrary, but actually documented and explainable.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.