Sql-server – Conversion failed when converting date and/or time from character string while inserting datetime sql server

sql serverssrs

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;

Best Answer

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.

Related Question