2

I'm working with a project in SSRS (SQL Server Reporting Services) and project is about employees of company,where i should find out age of 200 employees from their Social Security Number (CPR).

In Database i have a column called SSN and in this column , i have Social Security Numbers for all employees and all numbers look like this with out any ( - or /) example 1111111111 (DDMMYYXXXX) and Datatype is nvarchar(30).

When i executed my query ,i get error 'Conversion failed when converting date and/or time from character string'. Can someone please point me in the right direction. Thanks.

My query look like this : 

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder,
Users.SSN AS CPRNR, 

DATEDIFF(YEAR,CONVERT(date,STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'), 4), GETDATE()) As Age,
convert(varchar(10), Paychecks.WorkStartDate, 105) AS StartDato ,

Paychecks.DepartmentName AS Afdelinger

FROM dbo.Paychecks, dbo.Users
WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId order by Users.FirstName;
Improve this question

1 Answer 1

Reset to default
3

Bad news: social security number isn't in the format of DDMMYYXXXX. (My own SSN starts with a number above 3, for example.)

Take out the DATEDIFF and CONVERT functions, and just look at the source data. You'll likely see the offending rows just by eyeballing it:

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder, Users.SSN AS CPRNR, SupposedlyDate AS STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'), FROM dbo.Paychecks, dbo.Users WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId order by 4;

I don't know where you got that formula from, but it doesn't work. (It certainly doesn't produce a valid date for my own SSN.) There are ways to guess approximate dates from social security numbers, but not produce valid birth dates.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.