Some SQL environments allow to create so called user-defined functions (UDFs) to expand the coding capabilities beyond the default functions. Once specified, UDFs can be repeatedly called just like any other functions (SELECT, FROM etc.).
Here's an example of a UDF code that calculates age based on a selected date of birth:
CREATE FUNCTION AGE(dateofbirth DATE)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
SELECT
DATEDIFF(YEAR, dateofbirth, CURRENT_DATE()) AS age
$$
We define a UDF called age that takes a dateofbirth parameter of type Date and returns an Integer.
The function is defined using the LANGUAGE SQL clause, indicating that it's written in SQL.
We use $$ to delimit the start and end of the function body.
Inside the function body, we use the DATEDIFF function to calculate the difference in years between the dateofbirth and the current date (CURRENT_DATE()), which gives us the age of the person.
After creating the function, we can use it within SQL queries just like built-in functions.
SELECT
name
,dateofbirth
,AGE(dateofbirth) AS age
FROM employees
This query retrieves the name and date of birth of employees from a table named employees and calculates their age using the AGE UDF.