SQLTeam.com | Weblogs | Forums

Getting the age of a client from dob

HI I need to display the client age. is there a way within the Query not from a Stored proc,
that will give me the client age. The data looks like this:

2004-04-13 00:00:00.000

SELECT DATEDIFF(year, dob, CURRENT_TIMESTAMP)
	- CASE
		WHEN MONTH(CURRENT_TIMESTAMP) * 100 + DAY(CURRENT_TIMESTAMP)
			< MONTH(dob) * 100 + DAY(dob)
		THEN 1
		ELSE 0
	END AS age;

thank you, i just don't know where to integrate this in the above query. WHereever i put it, it gives an error, incorrect syntax.

Just replace dob with your column name. If you still cannot work it out provide consumable test data with expected results.

where do you place it?
select colums from tabel
inner joins
where a=a

where your code go?

We cannot see your computer remotely 'we have no such powers.
You mention an error but you have provided neither the error nor the code causing the error.

That's nasty fast because of the integer math BUT... it doesn't follow what most people consider to be the "Leap Year Day DoB" rule where if you were born on 29 Feb, then you recognized birthday is 28 Feb on non-leap years.

There are some states that say that if you were born on Feb 29, then your birthday is until 01 Mar. In that case, your code works a real treat.

If you MUST go by the earlier rule I mentioned, the following will work correctly at the expense of a bit of performance. (@pAsOf can be GETDATE() or Current_TimeStamp, just like yours)

 SELECT PassesLeapYear = DATEDIFF(yy,@DoB,@pAsOf) 
                       - IIF(@pAsOf < DATEADD(yy,DATEDIFF(yy,@DoB,@pAsOf),@DoB),1,0)
;