Search code examples
sqlsql-serverformatting

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?

Solution

  • 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.