SQLTeam.com | Weblogs | Forums

Using CASE to Replace NULL Value

I'll do my best to describe what I am attempting to perform.

My data has 2 fields, aside from many other.
The first is assigned the date/time the entry was made [Entered]
The second is assigned the date/time that the value was updated [Updated]

If [UpdatedDateTime] is NULL, that means the data was not adjusted from initial entry and I would like to return [EnteredDateTime] as the value.

If [UpdatedDateTime] is not NULL, then I want the [UpdatedDateTime] value to override the [EnteredDateTime] value.

[EnteredDateTime] will always have a value.

I want the values to be listed in the same column, I know I can have columns for each value, but trying to make the report cleaner.

I tried:

CASE UpdatedDateTime
	WHEN NULL THEN EnteredDateTime
END AS 'Entry Date/Time'

...but no values are listed when [UpdatedDateTime] is NULL.

I'm humbly sorry for not taking more time to think about other possibilities.

I've solved my dilemma.

I am using:

IIF( UpdatedDateTime IS NULL, EnteredDateTime,UpdatedDateTime) AS 'Entered Date/Time',

You could use
COALESCE(UpdatedDateTime, EnteredDateTime)
or
ISNULL(UpdatedDateTime, EnteredDateTime)

There are some differences between COALESCE and ISNULL, but for your example, either should work well. I prefer COALESCE in most cases.

Thank you, I've gone with option 2. Shorter/simpler than my IIF statements.