CODEWITHSUNDEEP
sqlsql-serverformatting
Johnny Bones
Johnny Bones

Reputation: 8404

Creating a unique ID that is a concatenation of several data types

I'm trying to make a unique ID in a view that is a concatenation of several data types. First I have DateID, which is a smallint, then I have LinkID which is an int, and lastly I have PicDateStamp which is a datetime.

I'm trying to create one unique ID, so I'm doing this:

Convert(varchar, T.DateID + '-' + P.LinkID + '-' P.PicDateStamp) as UTableID

For some reason it's returning a long date. I just want it to look something like (assume DateID is 22, LinkID is 74 and the date of PicDateStamp is 1/15/2018): 22-74-20180115.

What can I try next to resolve this?

Upvotes: 0

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Presumably you are using SQL Server (the + for string concatenation suggests this).

Convert to strings before concatenation:

(convert(varchar(255), T.DateID, 121) + '-' +
 convert(varchar(255), P.LinkID) + '-' +
 convert(varchar(255), P.PicDateStamp, 121)
) as UTableID

Note that this uses convert() rather than cast() so you can specify the format you want for the dates.

Upvotes: 1

Related Questions