Using IsNull with different data types in SQL SERVER

Question

Using IsNull with different data types in SQL SERVER

Salil Singh 60

Hi Team,

I am working on on premises SQL Server. I have a requirement where I need to apply left outer join between 2 tables and use IsNull to show default data when columns are having null. Under ISNULL, if a column is null what shall i put for below data types -

Varchar.
Int.
Decimal (6,0)
Date
DateTime

Please let me know if any other info required from me.

Thanks,

Salil

Venkata Ramanamma Uppu (Quadrant Technologies LLC) 0 Reputation points Microsoft External Staff

2025-12-05T05:48:58.35+00:00

Hi Salil Singh,

Thank you for reaching out to Q&A Forum.

Thank you Erland Sommarskog, SUMIT RAY and Naveen Kumar M for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..? Please let us know if you need any further assistance. We are happy to help.

4 answers

Your answer

Venkata Ramanamma Uppu (Quadrant Technologies LLC) 0 Reputation points Microsoft External Staff

2025-12-05T05:48:58.35+00:00

Hi Salil Singh,

Thank you for reaching out to Q&A Forum.

Thank you Erland Sommarskog, SUMIT RAY and Naveen Kumar M for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..? Please let us know if you need any further assistance. We are happy to help.

Answer 1

Erland Sommarskog 128.6K MVP Volunteer Moderator

For varchar, you would typically use the empty string:

isnull(stringcol, '')

For remaining data types, you would need to ask your business stakeholders what they want to see.

For numeric values 0 may make sense, but it can also be grossly misleading.

Many times, you want the value to be blank in the presentation layer, and the simplest way to achieve that is to return NULL and let the presentation layer do the job.

If you have a very primitive presentation layer, like SSMS, one alternative is to convert everything to string so that you can use a blank string to represent the NULL value. But this makes the code bulky, and also inflexible, particularly for date/time values, where you will have a fixed formatting. Date/time values are best formatted in the presentation layer according to the user's regional settings, so that one user can see 2025-06-01, and another can see 01/06/2025.

Salil Singh 60 Reputation points

2025-06-07T05:58:42.77+00:00

HI @Erland Sommarskog ,

Thank you for above reply.

Could you please provide examples for Date and DateTime2 use(with ISNULL). I am looking for standard way to use it.

Thanks,

Salil
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-07T08:17:09.7266667+00:00

The standard way is what I said: Return NULL to the presentation layer and have the presentation layer to deal with it. What drives you think this should be done in the database?
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T02:14:32.85+00:00

+1 to @Erland Sommarskog

what value you want to show in the UI totally depends on the business. If I am looking at a dashboard, I would prefer to see empty rather than fake values. So, it depends on the business leaders, what they want to see and it's not a database problem.
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T02:15:43.4+00:00

my comment was about specifically about date data type. Rest all can be empty or zero.
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-08T09:27:23.51+00:00
Rest all can be empty or zero.

I would disagree on that. Zero can be a correct way to represent a NULL value. For instance take this query:

SELECT SUM(amount) FROM accounts WHERE customer = 'Jane'

Jane does not have any accounts, so the query returns NULL. But that is obviously the same as her standing being 0 €.

On the other hand, consider a query that returns data about people, and one item return is the age, computed from the birth date. However, the birth date is not a mandatory to enter, so it will be NULL for some people. In this representing NULL as 0 would be very funny - not the least if the list of people can include newborns. Clearly it should be presented as empty in the presentation layer (or some other non-numeric value).
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T12:54:30.93+00:00

@Erland Sommarskog your example makes sense.
Salil Singh 60 Reputation points

2025-06-08T17:11:31.3833333+00:00

Hi @Erland Sommarskog , @balmukund

Thank you for above replies.

My requirement is bit different, none of the rows with Null columns will be shown to UI, such rows will be deleted from a different DB. Its a recent change where my SP now along with proper values would be generating Null values (for same columns). As C# code, earlier was getting returned values from SP as non null so chances are that receiving datatype in C# code was defined in a way to not handle null values, so I thought of making sure to handle null value in SP itself. Assigning a value to these null columns will not have any impact on UI as these such rows are to be deleted from a different DB.

Thanks,

Salil
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-08T17:18:01.8+00:00

That sounds even worse. You cannot invent values in the database. You will need to investigate what that C# code is doing in more detail, and particularly, you need to get full understanding of what it is doing with the columns from these left-joined tables.

Answer 2

SUMIT RAY 0

ISNULL(column, default)

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 4

You are applying a LEFT OUTER JOIN and using ISNULL to handle missing values.

For ISNULL(expression, replacement_value), the replacement must be of the same or implicitly convertible datatype as the column.

Suggested defaults for your data types:

Varchar: ISNULL(ColumnName, '') (empty string, or you can provide something like 'N/A' if business requires it)
Int: ISNULL(ColumnName, 0) (numeric 0 is safe; you can also choose -1 or another sentinel value if 0 is meaningful)
Decimal(6,0): ISNULL(ColumnName, 0) (decimal accepts 0.0; same rule as int)
Date: ISNULL(ColumnName, '1900-01-01') (SQL Server’s minimum safe date, often used as a default marker)
DateTime: ISNULL(ColumnName, '1900-01-01 00:00:00') (same as above, but with time part)

Consideration:

The replacement value must be a literal of the same type (or convertible).
For date/datetime, ensure the format is unambiguous (YYYY-MM-DD).
Sometimes COALESCE is better than ISNULL (it’s ANSI standard and allows multiple fallbacks).

Share via

Using IsNull with different data types in SQL SERVER

4 answers

Your answer