1

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

  • 2
    Your example gives 18 bytes for the current datetime. What do you want in the datetime? YYYYMMDDHHMMSS and 4 extra digits of precision, or more? – Philᵀᴹ Jul 2 '18 at 14:57
  • That Left(PolicyNumber, 10) could result in less than 10 characters. It should probably be changed to CAST(Left(PolicyNumber, 10) AS NCHAR(10)) – ypercubeᵀᴹ Jul 3 '18 at 7:27
2

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.

  • 2
    FORMAT() looks pretty, but it's expensive. Also, if you use sysdatetime() you can get much better precision than getdate() (7 of the 8 decimal places requested). – Aaron Bertrand Jul 2 '18 at 15:17
1

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.