Question
You have an Azure SQL database.
You need to create a scalar user-defined function (UDF) that returns the number of whole years between an input parameter named @OrderDate and the current date/time as a single positive integer. The function must be created in Azure SQL Database.
You write the following code.
```sql
01 CREATE FUNCTION dbo.ufnYearsSinceOrder (@OrderDate datetime2)
02 RETURNS int
03 AS
04 BEGIN
05
06 END
```
What should you insert at line 05?
- A. RETURN DATEDIFF(year, GETDATE(), @OrderDate);
- B. DATEDIFF(month, @orderdate, GETDATE()) / 12
- C. DATEPART(year, GETDATE()) - DATEPART(year, @orderdate)
- D. RETURN DATEDIFF(year, @OrderDate, GETDATE()); Correct
Explanation
A scalar function body must end with a RETURN statement, and DATEDIFF takes the start date before the end date, so DATEDIFF(year, @OrderDate, GETDATE()) gives a positive result for a past order. Option A reverses the arguments, which produces a negative number. Options B and C are bare expressions with no RETURN, so the function would not compile. DATEDIFF counts year boundaries crossed rather than elapsed full years, but among these options it is the only valid statement that returns a positive integer.
