CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 47

Concat a VarChar and Date field

I want to create another column where I concatenate ID and Date_. ID is Varchar(32) and Date_ is Datetime. When I concat the two and create a new column called UniqueValue I get the error message:

data type are incompatible.

How can I fix this?

SELECT        
    ID, PROP, DATE_, VALUE_, Model, ETLUPDATED, 
    CONCAT(ID, Date_) As UniqueValue
FROM            
    dbo.Retail2
WHERE        
    (PROP = 'vol') 
    AND (DATEDIFF(YEAR, DATE_, GETDATE()) <= 2) 
    AND (Model = 'base')

Upvotes: 1

Views: 984

Answers (1)

vincenthavinh
vincenthavinh

Reputation: 462

I guess you want your new column type to be VARCHAR.

You should try to convert the Datetime argument: Sql-server Convert() tutorial

Edit this part:

concat(ID, convert(VARCHAR(20), Date_, 120) )

You could also add another specific date format with the optional parameter. Replace 120 by whatever you need or remove it for default string format. (see doc linked)

Edit: it seems convert() is exclusive to SQL Server. Since you tagged SQL Server in your post it should work though.

Upvotes: 3

Related Questions