Click here to Skip to main content
15,888,221 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Fields type
------------------------
amonth int
ayear int
acombine varchar(8)


amonth | ayear | acombine
---------|---------------------
1 | 2016 | 201601
2 | 2016 | 201602
3 | 2016 | 201603
11 | 2016 | 201611
12 | 2016 | 201612


How do you concatenate 'amonth' and 'ayear' to get acombine?

Please assist

Thanks

What I have tried:

HAVE CHECKED ON IT FROM THE INTERNET
Posted
Updated 8-Mar-16 18:03pm
v3

1 solution

You have toi format the numbers by leading zero
try this
--create a temporary table
create table #tmp
(
amonth int ,
ayear int ,
acombine varchar(8)
)

--insert dummy data 
insert into #tmp values (1 ,2016 , '201601')
insert into #tmp values (2 , 2016 ,'201602')
insert into #tmp values (3 , 2016 , '201603')
insert into #tmp values (11  ,2016 , '201611')
insert into #tmp values (12 , 2016 , '201612')


--to get exact 'acombine' value  try this
select CONVERT(NVARCHAR, ayear)+ Right('00' +  Convert(varchar(10),amonth), 2) as acombine from #tmp 

OP will be 

201601
201602
201603
201611
201612


Thanks
 
Share this answer
 
Comments
Member 10744248 9-Mar-16 5:43am    
HAVING THIS ERROR MESSAGE






The conversion of the nvarchar value '201205' overflowed an INT2 column. Use a larger integer column. The conversion of the nvarchar value '201505' overflowed an INT2 column. Use a larger integer column.
Animesh Datta 9-Mar-16 5:55am    
Integer is defined as :

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.

To avoid this error, if you have a value in your VARCHAR column that is not within the range of an integer data type, convert the value into BIGINT

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900