Jeff,

I see a couple of minor issues with the function:

1. Results may be a little off if the @DOB and @Now are not exactly set to 00:00:00, so I recommend rounding them back to midnight. This would only be an issue on their birthday.

2. Results are a little odd if @Now is less than @DOB, so I recommend returning a NULL in that case. The concept of negative age doesn't seem to have any real world meaning anyway. And yes, I left this out of the code I posted also. :ermm:

Alternate version with suggested changes:

CREATE FUNCTION dbo.AgeInYears_mvj

(

@DOB DATETIME, --Date of birth or date of manufacture

@Now DATETIME --Usually, GETDATE() or CURRENT_TIMESTAMP but

--can be any date source like a column.

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

SELECT AgeInYears =

CASE

WHEN a.[Now] < a.[DOB]

THEN null

--If birthday hasn't happended yet this year, subtract 1.

WHEN DATEADD(yy, DATEDIFF(yy, a.[DOB], a.[Now]), a.[DOB]) > a.[Now]

THEN DATEDIFF(yy, a.[DOB], a.[Now]) - 1

ELSE DATEDIFF(yy, a.[DOB], a.[Now])

END

FROM

(SELECT[DOB] = dateadd(dd,datediff(dd,0,@DOB),0),

[Now] = dateadd(dd,datediff(dd,0,@Now),0)) a

;

GO

SELECT * FROM dbo.AgeInYears_MVJ('19601104 01:00','20121104')

SELECT * FROM dbo.AgeInYears('19601104 01:00','20121104')

SELECT * FROM dbo.AgeInYears_MVJ('19601104','19601103')

SELECT * FROM dbo.AgeInYears('19601104','19601103')

Results:

AgeInYears

-----------

52

AgeInYears

-----------

51

AgeInYears

-----------

NULL

AgeInYears

-----------

-1