[sql] Get 2 Digit Number For The Month

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?

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

Try:

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

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

Alternative to DATEPART

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

• SQLFiddle Demo

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

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)

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

Simply can be used:

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

Function

FORMAT(date,'MM') 

will do the job with two digit.

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.

For me the quickest solution was

DATE_FORMAT(CURDATE(),'%m')