Solved

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

Posted on 2014-04-15
6
1,752 Views
Last Modified: 2014-04-15
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
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 40002073
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
 
LVL 1

Author Comment

by:TBSupport
ID: 40002113
Thanks, Dulton!

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

Incorrect syntax near the keyword 'AS'.

TBSupport
0
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 40002120
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 22

Expert Comment

by:plusone3055
ID: 40002176
x
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002183
>> 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
 
LVL 1

Author Comment

by:TBSupport
ID: 40002344
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
too many installs coming along with SQL 2016? 1 34
If in a where clause in t-sql 7 52
Finding Where Clause Value in SQL Views and SP 21 62
Tracking Problematic Page Splits 1 49
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question