• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2054
  • Last Modified:

T-SQL: Concatenating two numbers--not adding them

Hello:

Below is my T-SQL script where I'm trying to take the last four digits of a Social Security Number and append with the year of that employees birthdate.


select right(UPR00100.SOCSCNUM, 10)+datepart(yy, UPR00100.BRTHDATE) from UPR00100

Instead, this script is mathematically adding the two numbers together.

What do I need to do to get the script to simply show me the two numbers combined and not added?

Thanks!

TBSupport
0
TBSupport
Asked:
TBSupport
1 Solution
 
DultonCommented:
you need to cast/convert the numbers to a string (varchar) in order to concatenate.


select Cast(right(upr00100.socscnum,10), AS VARCHAR(10)) + Cast(Year(upr00100.brthdate) AS VARCHAR(4)) as [MyField] FROM upr00100

Open in new window

0
 
TBSupportAuthor Commented:
Thanks, Dulton!

I'm getting the following error, upon running this:

Incorrect syntax near the keyword 'AS'.

TBSupport
0
 
DultonCommented:
My bad, I had a bogus comma in there.

select Cast(right(upr00100.socscnum,10) AS VARCHAR(10)) + Cast(Year(upr00100.brthdate) AS VARCHAR(4)) as [MyField] FROM upr00100

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
plusone3055Commented:
x
0
 
Scott PletcherSenior DBACommented:
>> I'm trying to take the last four digits of a Social Security Number and append with the year of that employees birthdate. <<

SELECT right(UPR00100.SOCSCNUM, 4) + convert(char(4), UPR00100.BRTHDATE, 112)
from UPR00100
0
 
TBSupportAuthor Commented:
Dulton is close.  But, it is not concatenating the two fields.  It has approximately four spaces between them.

Here is the revised and correct solution:

select rtrim(Cast(right(upr00100.socscnum,10) AS VARCHAR(10)))
+ Cast(Year(upr00100.brthdate) AS VARCHAR(4)) as test from UPR00100

Thanks, Dulton!

TBSupport
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now