How to Concatenate DateTime to nvarchar field

Question

I have a table with a nvarchar(32) column and I want to update the value in this column with the first 10 bytes of another field on the table which is defined as nvarchar(32) after the 10 bytes I want to concatenate the first 22 bytes of the current datetime without the dashes in the date.

Update Bil_ReturnsRepository 
  Set PaymentReference = CAST((Left(PolicyNumber, 10) + CONVERT(datetime,GETDATE(), 121)) as nvarchar
Where PolicyNumber = '1234567890'

I would like for the output to look like this for example:

1234567890201802130446386883

Answer 1

Datetime without punctuation and up to 7 second decimals are 21 digits so there would be 1 character (32 - 10 - 21) still missing.

You can use the following to format the way you want.

Update Bil_ReturnsRepository Set 
    PaymentReference = CONVERT(NVARCHAR(32), 
        CONCAT(
            LEFT(PolicyNumber, 10), 
            FORMAT(SYSDATETIME(), 'yyyyMMddHHmmssfffffff0')))
Where 
    PolicyNumber = '1234567890'

If PolicyNumber is already NVARCHAR then there is no need to do an explicit CONVERT. Notice that I hard-coded the last zero.

Answer 2

While FORMAT() might be slightly more readable, there is a significant performance penalty (about 2X in my testing). If this is something you're going to do once, carry on. If this is something you're going to be repeatedly, especially as this table gets larger, this should allow you to scale about twice as well:

UPDATE dbo.Bil_ReturnsRepository 
  SET PaymentReference = LEFT(PolicyNumber, 10) 
    + REPLACE(REPLACE(CONVERT(char(8), sysdatetime(), 112)
    + CONVERT(char(14), sysdatetime(), 14),':',''),'.','')
WHERE PolicyNumber = '1234567890';