SQL Server NULLvalues and Concatenation

Question

Most of my tables have four fields that include:

Created datetime not null default = GetDate()
CreatedBy varchar(50) not null default = suser_sname()
Updated datetime null
UpdatedBy Varchar(50) null

For data that I display in a grid, I want to combine these fields to show the "Last Edit" in the form of "datetime" by "user".

Knowing that my Created and CreatedBy fields will never be null, Coalesce seems to be the answer but I'm getting errors with the following:

,Convert(varchar(20),Coalesce(i.Updated,i.Created) ,120) + ' by ' + Coalesce(i.UpdatedBy,i.Created) as [LastEdit]

Gordon Linoff · Accepted Answer · 2014-09-06 15:10:55Z

3

In all likelihood, the problem is the second coalesce(). I am guessing you mean:

(Convert(varchar(20), Coalesce(i.Updated, i.Created), 120) + ' by ' +
 Coalesce(i.UpdatedBy, i.CreatedBy)
--------------------------------^
) as [LastEdit]

You were trying to combine a date with a string, which can cause a problem.

answered Sep 6, 2014 at 15:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bsivel Over a year ago

Thanks Gordon - dumb mistake on my part. Created needs to be CreatedBy in the second coalesce.

Brian Nordberg · Accepted Answer · 2014-09-06 18:16:55Z

1

Be wary of SQL Server's Concatenate Null Yields Null setting. You may want to either set it to off during your query execution. Or if you have SQL 2012 or higher, use the Concat() function. http://msdn.microsoft.com/en-us/library/hh231515.aspx - concat http://msdn.microsoft.com/en-us/library/ms176056.aspx - set concat_null_yields_null

answered Sep 6, 2014 at 18:16

Brian Nordberg

863 bronze badges

Collectives™ on Stack Overflow

SQL Server NULLvalues and Concatenation

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related