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?

301582 次浏览

小开

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

小开

Alternative to DATEPART

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

SQLFiddle Demo

小开

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)

小开

Try:

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

小开

最佳答案

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.

小开

Function

FORMAT(date,'MM')

will do the job with two digit.

小开

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

小开

For me the quickest solution was

DATE_FORMAT(CURDATE(),'%m')

小开

Simply can be used:

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

小开

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.

小开

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

小开

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