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;
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.