Sql-server – SSRS report expression FormatDateTime changes Nulls to ‘1/1/0001’

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.

So for anyone who is interested, I figured out this was a permissions issue. As this is a report, it runs under a limited permissions login. I'd granted that login read permissions to Master and View Server State, which I thought would be enough, and sure enough, the report loads, but it seems like for some reason, if you don't have View Any Definition, your results may not be complete. I suspect (though I don't really have time to prove it) that each visible AG had at least one DB with some sort of relevant permissions, bringing it into view, but anyway, View Any Definition fixed it :) thanks for all the suggestions though