Lets say you have a period value representing a month 1 - 12, but the business require the string to be padded with a zero. e.g. 01,02 … 10,11,12

This can be accomplished using the FORMAT function

1
2
3
4
5
6
DECLARE @period INT = 1;
SELECT FORMAT(@period, 00); --Returns 01
GO
DECLARE @period INT = 10;
SELECT FORMAT(@period, 00); --Returns 10
GO