Get 2 Digit Number For The Month
SqlSql ServerSql Server-2008TsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
Function
FORMAT(date,'MM')
will do the job with two digit.
Solution 2 - Sql
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 3 - Sql
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 - Sql
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 - Sql
CONVERT(char(2), getdate(), 101)
Solution 6 - Sql
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))
Solution 7 - Sql
Solution 8 - Sql
For me the quickest solution was
DATE_FORMAT(CURDATE(),'%m')
Solution 9 - Sql
Simply can be used:
SELECT RIGHT('0' + CAST(MONTH(@Date) AS NVARCHAR(2)), 2)
Solution 10 - Sql
Try:
select right ('0'+convert(nvarchar(2), DATEPART(mm, getdate())),2 )
Solution 11 - Sql
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.
Solution 12 - Sql
SELECT REPLACE(CONVERT(varchar, MONTH(GetDate()) * 0.01), '0.', '')