How to combine 2 int value columns to varchar with specified length

Question

i would like to combine this two int columns into one:

Month   Year
1       2017  
2       2016
12      2016

i used SELECT CAST(Year AS nvarchar(20)) + '-' + CAST(Month AS nvarchar(20)) AS newcolumn from table_name

however, the result that i got is

newcolumn
2017-1
2016-2
2016-10

my desired result is

newcolumn
2017-01
2016-02
2016-10

im using ms sql server.

Thanks

What version of sql server? Bennjoe's answer will work; SQL Server 2012 and after has additional options. — MPR, Mar 30 '17 at 01:58

score 0 · Accepted Answer · answered Mar 30 '17 at 01:52

You can pad the month format so that it is forced to display 2 numbers.

SELECT CAST(Year AS nvarchar(20)) + '-' 
+ RIGHT('00' + CAST(Month AS nvarchar(20)),2) AS newcolumn from table_name

Sample with Getdate():

SELECT CAST(YEAR(GETDATE()) as nvarchar(20)) + '-' +
       RIGHT('00' + CAST(MONTH(GETDATE()) as NVARCHAR(2)),2)

1 Answers1