Calculate age with int field 2

[lambic]

Hi there,

I've read a few posts about calculating an age with the 'datediff()' function, however I have a date of birth field which is an 'int' data type, with the date held in YYYYMMDD format, e.g.:

19450130

Can anybody suggest how I can calculate a persons current age, with data held in this way?

Many Thanks

 

[hmckillop]

you are going to need to manipulate the date you have into a date format and then use a date function.
something like this would work.

select datediff(yy, 	convert(datetime, 
				convert(varchar,substring(convert(varchar,mydate),5,2)) 
				+ '-' + 
				convert(varchar,right(mydate,2)) 
				+ '-' + 
				convert(varchar,(left(mydate,4)) )
			) , getdate())

	from MyTable

"I'm living so far beyond my income that we may almost be said to be living apart

 

[lambic]

Thankyou hmckillop, I think that will do the trick!

Cheers

 

[JamesLean]

To convert the int to a datetime you just need to CAST it as a varchar and let SQL Server implicitly convert to a datetime if it needs to. You can then work out the age something like this (only an example - there may be a neater way!):

DECLARE @dob int,
	@today datetime

SET @dob = 19781125
SET @today = GETDATE()

SELECT DATEDIFF(yy, CAST(@dob AS varchar), @today) -
	CASE WHEN MONTH(CAST(@dob AS varchar)) > MONTH(@today)
		OR (MONTH(CAST(@dob AS varchar)) = MONTH(@today) AND DAY(CAST(@dob AS varchar)) > DAY(@today)) THEN 1
		ELSE 0
		END AS age

--James

 

[lambic]

Thanks James!