# 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 ENCRYPTION AS BEGIN DECLARE @DDATE AS DATE, @DNEAREST AS DATE, @IYEARDIFF AS INT, @IMONTHDIFF AS INT, @IDAYDIFF AS INT SET @DDATE = DATEFROMPARTS(@IYEAR, @IMONTH, @IDAY) SET @DNEAREST = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(@DDATE)) SET @IYEARDIFF = DATEDIFF(YY, @DDATE, GETDATE()) - CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END SET @IMONTHDIFF = (DATEDIFF(MM, @DDATE, GETDATE()) - CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END) % 12 SET @IDAYDIFF = CASE WHEN @DNEAREST <= GETDATE() THEN DATEDIFF(DD, @DNEAREST, GETDATE()) ELSE DATEDIFF(DD, DATEADD(MM, -1, @DNEAREST), GETDATE()) END INSERT INTO @AgeTable VALUES (@IYEARDIFF, @IMONTHDIFF, @IDAYDIFF) RETURN END ```
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 INT WITH ENCRYPTION AS BEGIN DECLARE @DNEAREST AS DATE, @IRETURN AS INT SET @DNEAREST = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(@DDATE)) IF @CDATE_PART = 'YY' BEGIN SET @IRETURN = DATEDIFF(YY, @DDATE, GETDATE()) - CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END END IF @CDATE_PART = 'MM' BEGIN SET @IRETURN = (DATEDIFF(MM, @DDATE, GETDATE()) - CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END) % 12 END IF @CDATE_PART = 'DD' BEGIN SET @IRETURN = CASE WHEN @DNEAREST <= GETDATE() THEN DATEDIFF(DD, @DNEAREST, GETDATE()) ELSE DATEDIFF(DD, DATEADD(MM, -1, @DNEAREST), GETDATE()) END END```

``` ```

```RETURN @IRETURN END ```

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```