CODEWITHSUNDEEP
sqlsql-server
Johnny Bones
Johnny Bones

Reputation: 8404

Concatenate fields with different data types

I'm trying to concatenate fields with VarChar and Date into one VarChar field for a database of tourdates. So, imagine a table of, say, Madonna tour dates as such:

TourName           TourStDt          TourEndDt
Like A Virgin      3/5/1985          12/1/1985
Material Girl      1/15/1986         10/10/1987

I'm sure the dates aren't accurate, but whatever... Anyway, so I want to create a query that concatenates the TourName, TourStDt and TourEndDt fields so that it looks like this:

Like A Virgin (3/5/1985 to 12/1/1985)
Material Girl (1/15/1986 to 10/10/1987)

I wrote a query like this:

Select DISTINCT 
    TourID, 
    TourName + '(' + TourStDt + ' to ' + TourEndDt + ')' AS TourName2
from [tblTours] 
ORDER BY [TourID] ASC

When I do this, I get an error:

The data types nvarchar and date are incompatible in the add operator.

Can anyone tell me how to produce the results I outlined above?

Upvotes: 0

Views: 3364

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You need to convert the values to strings:

Select DISTINCT TourID, 
       TourName + '(' + convert(varchar(255), TourStDt) + ' to ' + convert(varchar(255), TourEndDt) + ')' AS TourName2
from [tblTours] 
ORDER BY [TourID] ASC ;

I would suggest that you add a third argument to convert() specifying the date format.

Upvotes: 2

Related Questions