Left fill variable with zeros in FORMATMESSAGE

Question

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.

@dvo Nope, I want to do it within the format string. I don't even want to convert the dates, but I have to. — Count Boxer, Sep 25 '19 at 20:48

GSazheniuk · Answer 1 · 2019-09-25T20:55:19.233

2

UPDATE: it looks like FormatMessage supports more formatting options and you can easily specify as many leading 0's as you like:

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

edited Sep 25 '19 at 20:55

answered Sep 25 '19 at 20:45

GSazheniuk

1,340
10
16

I want to do it within the format string. Other languages like Java have this feature. – Count Boxer Sep 25 '19 at 20:50
1

Plus one for the link. It would also be nice if you could directly insert a date without casting it to a string. Because I use octal and hex so much more than date! – Count Boxer Sep 25 '19 at 23:14

score 0 · Accepted Answer · answered Sep 25 '19 at 20:52

0

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))

answered Sep 25 '19 at 20:52

dvo

2,113
1
8
19

1

I was composing my answer as you were composing yours. Thanks! – Count Boxer Sep 25 '19 at 20:55
@CountBoxer Nice solution. Glad you found something to work – dvo Sep 25 '19 at 20:56

score 0 · Answer 3 · answered Sep 25 '19 at 20:54

Looking at how Java does it solved my problem.The first 0 is to zero fill and the second number is the number of columns to use.

Run time #%02d: From %s to %s

Returns:

Run time #08: From 03/21/2019 to 04/21/2019
Run time #09: 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

Using:

Run time #%-4d: From %s to %s

left justifies in four columns. Cool!

Left fill variable with zeros in FORMATMESSAGE

3 Answers3