Get 2 Digit Number For The Month

there are different ways of doing it

• Using RTRIM and specifing the range:

like

SELECT RIGHT('0' + RTRIM(MONTH('12-31-2012')), 2); 

• Using Substring to just extract the month part after converting the date into text

like

SELECT SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)

see Fiddle

There may be other ways to get this.

Function

FORMAT(date,'MM') 

will do the job with two digit.

Another simple trick:

SELECT CONVERT(char(2), cast('2015-01-01' as datetime), 101) -- month with 2 digits
SELECT CONVERT(char(6), cast('2015-01-01' as datetime), 112) -- year (yyyy) and month (mm)

Outputs:

01
201501

Pinal Dave has a nice article with some examples on how to add trailing 0s to SQL numbers.

One way is using the RIGHT function, which would make the statement something like the following:

SELECT RIGHT('00' + CAST(DATEPART(mm, @date) AS varchar(2)), 2)