Take the 2-minute tour ×

I have an integer column "Month" I would like to get 2 digit number for month.

This is what I have tried: DATEPART(mm, @Date)

It returns one digit for months January to September I am using SQL Server 2008

Anyone has suggestion?

share|improve this question
See this thread for a plethora of options: stackoverflow.com/questions/1914682/… –  Mark S. Rasmussen Feb 20 '13 at 9:09
I came across across this with the same question but for SQL Server 2012. Based on user3829053's answer I worked out that for that version at least, select format(month(@Date),'00'), or select format(datepart(mm, @Date),'00') works. –  David Oct 15 at 1:56

6 Answers 6

up vote 16 down vote accepted

there are different ways of doing it

  • Using RTRIM and specifing the range:


SELECT RIGHT('0' + RTRIM(MONTH('12-31-2012')), 2); 
  • Using Substring to just extract the month part after converting the date into text


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

see Fiddle

There may be other ways to get this.

share|improve this answer
I am not getting the leading zero. Since the column type is integer, how will that have efect on the result? Does the column type has to be char in order to function? –  Adam Feb 20 '13 at 9:35
it seems atributte type has to be char. Then I get the desired output. I presume there is no way to get the same result with integer as atrubutte type. Or? –  Adam Feb 20 '13 at 9:40
it will be much easier if you can change the format of the month where you wish to compare the month. like if you get the value of @month int = 2 and you want to compare it value of /@month_compare varchar(20) with value '02' then just cast the /@month_compare to int before doing so else change the data type of month column. –  ankit suhail Feb 20 '13 at 11:37

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)
share|improve this answer
I am not getting the desired result. is it possible that sql server management studio is set to ignore leading nulls? –  Adam Feb 20 '13 at 9:15
@Adam - Try seeing if replacing @date with GETDATE() makes a difference? –  SchmitzIT Feb 20 '13 at 9:19

Alternative to DATEPART

share|improve this answer

Function FORMAT(date,'MM')

do the job - two digit

share|improve this answer


select right ('0'+convert(nvarchar(2), DATEPART(mm, getdate())),2 )
share|improve this answer

append 0 before it by checking if the value falls between 1 and 9 by first casting it to varchar

select case when DATEPART(month, getdate()) between 1 and 9 
then '0' else '' end + cast(DATEPART(month, getdate()) as varchar(2))
share|improve this answer
the attribute type is integer. –  Adam Feb 20 '13 at 9:18

Your Answer


