Search code examples
sql-servert-sqlformatmessage

Left fill variable with zeros in FORMATMESSAGE

Using SQL Server's FORMATMESSAGE command, I am printing status messages:

PRINT FORMATMESSAGE('Run time #%2d: From %s to %s', @i, CONVERT(VARCHAR(10), @from_date, 101), CONVERT(VARCHAR(10), @to_date, 101))

This gives me output like:

Run time # 8: From 03/21/2019 to 04/21/2019
Run time # 9: From 04/21/2019 to 05/21/2019
Run time #10: From 05/21/2019 to 06/21/2019
Run time #11: From 06/21/2019 to 07/21/2019

But how do I zero fill if the @i variable is less than 10. It gives me the two positions, but I can't figure out the character to left fill with zeros.

Solution

  • Try %0{# of digits}i (%02i) like so: 

    Declare @i int = 2

PRINT FORMATMESSAGE('La La La: %02i', @i)

-- OUTPUT: 'La La La: 02'

    Your string: 

    PRINT FORMATMESSAGE('Run time #%02i: From %s to %s', @i, CONVERT(VARCHAR(10), @from_date, 101), CONVERT(VARCHAR(10), @to_date, 101))