Get 2 Digit Number For The Month

Question

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?

Solution 1

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.

Solution 2

Function

FORMAT(date,'MM') 

will do the job with two digit.

Solution 3

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)

Solution 4

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

Solution 5

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

View more solutions

Comments

[Adam]

I am not getting the desired result. is it possible that sql server management studio is set to ignore leading nulls?

[Adam]

the attribute type is integer.

[SchmitzIT]

@Adam - Try seeing if replacing @date with GETDATE() makes a difference?

[Adam]

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]

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?

[Ankit Suhail]

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.

[mcNux]

Added in SQL Server 2012. The question states using 2008.

[Thronk]

This is neither tsql nor Sql server 2008 which was tagged in the question.

[Jan Papež - honyczek]

I was searching for solution like this, because i know this function from Python. Actually I didn't know it is new from 2012.

[Scott]

This worked great! A note to say that this requires the input to be a date variable, not just look like one, e.g. FORMAT('2017-01-01', 'MM') fails but FORMAT(GETDATE(), 'MM') works.

[aruno]

GETDATE() will return '2018-10-29' so this gives you 20. While this may have worked for you with your configuration this isn't a safe way to achieve this.

[AndrewR]

No, @Simon_Weaver, GETDATE() will get you a date, convert with parameter 101 will put month first, docs.microsoft.com

[aruno]

@Vinay sorry - you were right. I see 101 = mm/dd/yyyy so this will always work wherever your server is based. thanks @andrewr for correcting me. [I added a single space to the answer so as to be able to upvote it]

[Vi8L]

Modified for MariaDB SELECT RIGHT(concat('0', RTRIM(MONTH('2013-12-12'))),2)

[β.εηοιτ.βε]

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

[drapkin11]

@Scott, Welcome to SO! To add to what "β.εηοιτ.βε" said above, mentioning how your answer addresses something that previous answers don't would be helpful as well.