# Nested functions

Scalar and aggregate functions can be nested in several ways.

You can nest functions in the following ways:

- Example: Scalar functions within scalar functions
- Suppose that you want to know the month and day of hire for a particular employee in department D11. Suppose that you also want the result in USA format (mm/dd/yyyy). Use this query:
`SELECT SUBSTR((CHAR(HIREDATE, USA)),1,5) FROM EMP WHERE LASTNAME = 'BROWN' AND DEPT = 'D11';`

The result table looks like this:

`===== 03/03`

- Example: Scalar functions within aggregate functions
- In some cases, you might need to invoke a scalar function from within an aggregate function. Suppose that you want to know the average number of years of employment for employees in department A00. Use this query:
`SELECT AVG(DECIMAL(YEAR(CURRENT DATE - HIREDATE))) FROM EMP WHERE DEPT = 'A00';`

The result table looks like this:

`======= 20.6666`

The actual form of the result, 20.6666, depends on how you define the host variable to which you assign the result.

- Example: Aggregate functions within scalar functions
- Suppose that you want to know the year in which the last employee was hired in department E21. Use this query:
`SELECT YEAR(MAX(HIREDATE)) FROM EMP WHERE DEPT = 'E21';`

The result table looks like this:

`==== 2002`