How to Concatenate DateTime to nvarchar field 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
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.