# SQL User-defined Function for Calculating Age as of Today

Every now and then we needÂ  an easy way to calculate age of something in a database application, e.g. employee age. Here I propose two SQL user-defined functions that can be used for the job. The first one is a table-valued function, and the second one is scalar-valued.

First function: FT_Age (table-valued)

`CREATE FUNCTION [dbo].[FT_Age](@IYEAR int,@IMONTH int,@IDAY int)RETURNS @AgeTable TABLE(IYEAR int, IMONTH int, IDAY int)WITH ENCRYPTIONASBEGINDECLARE @DDATE AS date,@DLAST_MONTHLY AS date,@ILAST_MONTH AS int,@IMONTHLY_DAY AS int,@DTODAY AS date,@IYEARDIFF AS int,@IMONTHDIFF AS int,@IDAYDIFF AS intSET @DTODAY = GETDATE()SET @DDATE = DATEFROMPARTS(@IYEAR, @IMONTH, @IDAY)SET @ILAST_MONTH = MONTH(DATEADD(MM, -1, @DTODAY))SET @IMONTHLY_DAY = CASE WHEN @ILAST_MONTH = 2 AND DAY(@DDATE) > 28 THEN 28WHEN @ILAST_MONTH IN (4,6,9,11) AND DAY(@DDATE) > 30 THEN 30ELSE DAY(@DDATE) ENDSET @DLAST_MONTHLY = DATEFROMPARTS(YEAR(@DTODAY), @ILAST_MONTH, @IMONTHLY_DAY)SET @IYEARDIFF = DATEDIFF(YY, @DDATE, @DTODAY)- CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, @DTODAY), @DDATE) > @DTODAY THEN 1 ELSE 0 ENDSET @IMONTHDIFF = (DATEDIFF(MM, @DDATE, @DTODAY)- CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, @DTODAY), @DDATE) > @DTODAY THEN 1 ELSE 0 END) % 12SET @IDAYDIFF = DATEDIFF(DD, @DLAST_MONTHLY, @DTODAY)INSERT INTO @AgeTable VALUES (@IYEARDIFF, @IMONTHDIFF, @IDAYDIFF)RETURNEND`

Usage example:
`SELECT * FROM FT_Age(1945, 8, 17)`

Second function: FN_Age (scalar-valued)

``````CREATE FUNCTION [dbo].[FN_Age](@DDATE date,@CDATE_PART char(2))RETURNS INTWITH ENCRYPTIONASBEGIN
DECLARE @DLAST_MONTHLY AS date,@ILAST_MONTH AS int,@IMONTHLY_DAY AS int,@DTODAY AS date,@IRETURN AS int SET @DTODAY = GETDATE()SET @ILAST_MONTH = MONTH(DATEADD(MM, -1, @DTODAY))SET @IMONTHLY_DAY = CASE WHEN @ILAST_MONTH = 2 AND DAY(@DDATE) > 28 THEN 28WHEN @ILAST_MONTH IN (4,6,9,11) AND DAY(@DDATE) > 30 THEN 30ELSE DAY(@DDATE) ENDSET @DLAST_MONTHLY = DATEFROMPARTS(YEAR(@DTODAY), @ILAST_MONTH, @IMONTHLY_DAY)IF @CDATE_PART = 'YY' BEGINSET @IRETURN = DATEDIFF(YY, @DDATE, GETDATE()) - CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 ENDEND
IF @CDATE_PART = 'MM' BEGINSET @IRETURN = (DATEDIFF(MM, @DDATE, GETDATE()) - CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END) % 12END
IF @CDATE_PART = 'DD' BEGINSET @IRETURN = DATEDIFF(DD, @DLAST_MONTHLY, @DTODAY)END``````RETURN @IRETURNEND
``````

Usage example:
```SELECT RTRIM(CONVERT(CHAR(4), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'YY'))) + 'y ' + RTRIM(CONVERT(CHAR(2), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'MM'))) + 'm ' + RTRIM(CONVERT(CHAR(2), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'DD'))) + 'd ' AS Age```