Get 2 Digit Number For The Month

72
votes

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?

12
sqlsql-serversql-server-2008tsql
See this thread for a plethora of options: stackoverflow.com/questions/1914682/… – Mark S. Rasmussen
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

12 Answers

98
votes

Function

FORMAT(date,'MM')

will do the job with two digit.

86
votes

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.

17
votes

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

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
4
votes

CONVERT(char(2), getdate(), 101)

3
votes

Alternative to DATEPART

SELECT LEFT(CONVERT(CHAR(20), GETDATE(), 101), 2)
3
votes

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

For me the quickest solution was

DATE_FORMAT(CURDATE(),'%m')
2
votes

Simply can be used:

SELECT RIGHT('0' + CAST(MONTH(@Date) AS NVARCHAR(2)), 2)
1
votes

Try:

select right ('0'+convert(nvarchar(2), DATEPART(mm, getdate())),2 )
1
votes

My way of doing it is:

right('0'+right(datepart(month,[StartDate]),2),2)

The reason for the internal 'right' function is to prevent SQL from doing it as math add - which will leave us with one digit again.

0
votes
SELECT REPLACE(CONVERT(varchar, MONTH(GetDate()) * 0.01), '0.', '')