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?

Palaeography

See this thread for a plethora of options: #1915182 – Hydromechanics

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. – Deragon

Answer 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.

Muth

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? – Palaeography

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? – Palaeography

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. – Muth

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

Answer 2

Function

FORMAT(date,'MM') 

will do the job with two digit.

Tiruchirapalli

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

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

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. – Abrasive

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

Redintegration

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

@Palaeography - Try seeing if replacing @date with GETDATE() makes a difference? – Redintegration

Answer 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

Visigoth

Answer 5

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

Mccaffrey

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. – Kingship

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

@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] – Kingship

Answer 6

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

Glomma

the attribute type is integer. – Palaeography

Answer 7

Alternative to DATEPART

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

• SQLFiddle Demo

Wellman

Answer 8

For me the quickest solution was

DATE_FORMAT(CURDATE(),'%m')

Wendelin

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

Answer 9

Simply can be used:

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

Meuser

Answer 10

Try this command:

SELECT FORMAT(MONTH(CAST(GETDATE() AS DATE)), '00') AS MonthNumberFormatted;

It will format month number in 2 digit format.

Revision

Answer 11

Try:

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

Syverson

Answer 12

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.

Insnare

Answer 13

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

Abrasive

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. – Smidgen

@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. – Johannessen