Date Value Incorrect When Converting To Interger

Question

Hi,

I am attempting to recreate a concat that I use in MS Excel, so I can use this in SQL. The concat consists of (First 3 letters of) Forename, Surname and DOB as a value.
I am getting slightly different results in SQL and I wonder if it is rounding the DOB value.

For example:
John Smith 11/05/2010

EXCEL:
=SUBSTITUTE(CONCATENATE(TRIM(M4),LEFT(TRIM(L4),3),VALUE(N4))," ","")
SmithJoh40309

SQL:
CONCAT (FAM_MEMBER.SURNAME, LEFT(FAM_MEMBER.FORENAME,3),CONVERT(INT,FAM_MEMBER.DATE_OF_BIRTH)) AS 'CONCAT',
SmithJoh40307

As you can see, its the DOB part which isnt quite right. Any advise on this, I would be very grateful! Thanks

Answer 1

Both MS Excel and SQL Server stores dates as numeric values and calculate the date on the fly.
But both use different zero-bases, you see it when you convert numeric 0 to a date; then return different dates.