SQL Server comes with a number of different types of built-in functions.  One of these types of functions is called “Scalar”.  A scalar function is a function that when called returns only a single value.

The other category of built-in functions are aggregate, analytic, and rowset, functions.  Some information about these other types of function can be found in other chapters of this book.  For a complete reference to all the different types of built-in functions within SQL Server, please refer to the Microsoft documentation here.

In order to understand scalar functions, I will explore the different categories of scalar functions first.  I will follow up by providing a few examples of how to use some of the more commonly used scalar functions.  Let’s dig into scalar functions in detail.

Categories of Scalar Functions

As already stated, a scalar function is a function that returns only a single value.  They might require no parameters or may require one or more parameters.  Scalar functions can be used anywhere an expression can be used.  There are a lot of built-in scalar functions in SQL Server, too many to list in this chapter. Each function will fall into one of the following sub-categories of scalar functions listed in Table 1

Table 1: Subcategories of scalar functions

Determinism of a Function

Prior to getting into examples of how to use scalar functions, let me first discuss an important concept about functions, known as determinism. The determinism of a function can be either deterministic or non-deterministic.  A deterministic function is a function that, when called with the same input value multiple times, will return the same output value each time it’s called. Whereas a non-deterministic function, when called numerous times with the same values, may or may not return the same results.  For example, if you call the deterministic string function LEN and pass it a parameter value of “This is my string.”, it will always return the integer value “18”, because that is the length of the string. Whereas if you call the non-deterministic Date/Time function GETDATE() which excepts no parameter, it will always return a different value, the current date and time. In Table 2, you can find a list of built-in deterministic functions.  Whereas in Table 3, you can find a list of built-in non-deterministic functions.

Table 2: List of Built-In Deterministic Functions

Table 3: List of Non-Deterministic Built-In Functions

Database Used for Examples

In order to demo some of the different built-in scalar functions, I will need a database for testing my sample queries.  The database I will be using for my examples will be the SQL Server sample database known as AdventureWorks2017.  If you want to follow along and run the examples in this article, you can download a backup of the AdventureWorks2017 database from here.

Examples of using Scalar functions

A Scalar function is a function that returns a single value.  There are a number of different built-in scalar functions, as shown in “Scalar Functions” section above.  In order to show how a scalar function works and how expressions can be pass as parameters to a function, I will be providing several examples.

Date/Time Function Examples

Date/Time functions allow you to generate and manipulate date/time values in SQL Server.  For instance, you might just want to know the current date and time.  Or maybe you want to calculate the difference between two dates or validate that a string value contains a valid date. These are just a few examples of how you might use a date/time function in your TSQL code.  For a complete list of date/time functions review the Microsoft date and time documentation, which can be found here.  To get a better sense of how to use date/time functions, I will provide a few examples of some commonly used date/time functions.

Using the GETDATE() Function

There are many different situations where your applications might need to know “What is the current date and time?”.  The built-in GETDATE() function returns the current system timestamp as a datetime value without a time zone offset.  The GETDATE() function does not require any parameters, as shown in the Microsoft Documentation found here.  The code in Listing 1 shows how to use the GETDATE() function to display the current date/time.

Listing 1: Returning the current date/time

SELECT GETDATE() AS CurrentDateTime;

As you can see here, I called the GETDATE() function without passing any parameters.  When I ran the code in Listing 6, it produced the results found in 1.

Figure 1: Results when executing code in Listing 1

By reviewing the results in Figure 1, you can see that the current date/time was displayed as a DATETIME data type value.

Using the DATEDIFF function

There may be times when an application needs to compare two dates to determine how far apart the dates might be.  The DATEDIFF functions can be used to return a count of the number of specific date part boundaries crossed between two different date/time values.  The syntax for the DATEDIFF function as found in the Microsoft Documentation can is displayed in Figure 2.

Figure 2: Syntax for DATEDIFF function

To better understand how to use the DATEDIFF function, I will run some code that will find all the OrderDate’s, in the SalesOrderHeader table, that were placed in the first 65 days of the second quarter of 2011.  The code for this example can be found in Listing 2.

Listing 2: DATEDIFF function example

USE AdventureWorks2017;
GO
SELECT DISTINCT(OrderDate)
               , DATEDIFF(day, DATEFROMPARTS('2011','04','01'), OrderDate) as DaysBetween
FROM Sales.SalesOrderHeader
WHERE DATEDIFF(day, DATEFROMPARTS('2011','04','01'), OrderDate) < 65
ORDER BY OrderDate;

By reviewing the code in Listing 1, you can see that I used the DATEDIFF function not only in the select list but also in the WHERE clause. I also used an expression for the second parameter of this function. The expression used was the DATEFROMPARTS function, which is another scalar date function.  I used this function to specify the startdate parameter. The DATEFROMPARTS function uses three different parameters values (year, month, and day) that are used to construct a DATE type value.  For more information on the DATEFROMPARTS function, please review the Microsoft document, found here. In this example, I am displaying all of the SalesOrderHeader rows where the difference between the startdate and enddate parameters of the DATEDIFF functions are less than 65 from the start date of the second quarter in 2011.

When the code in Listing 2 is run, the results found in Figure 3 are returned.

Figure 3: Results from running code in Listing 2.

As you can see, the DATEDIFF function made it very easy for me to calculate the number of days, as an integer value, between two different dates.

Additionally, in this example, I used an expression for the startdate parameter, in this cause another function call.  You can use expressions as parameters to functions as long as the expression equates to a value that has an appropriate data type for the function parameter.

Using the DATEADD function

There may be a time when you need to take a date and add or subtract a number of date units from it to derive another date.  The DATEADD function performs those kinds of date math calculations.   The syntax for the DATEADD function, as found in the Microsoft documentation, is shown in Figure 4.

Figure 4 Syntax for DATEADD function

To show an example of how to use this function, let me calculate the anticipated retirement vesting date, assuming it takes five years for an employee to become vested. The code in Listing 3 uses the DATEADD function to calculate the retirement vesting date for the top 5 employees based on HireDate.

Listing 3: Code to demo the DATEADD function

USE AdventureWorks2017;
GO
SELECT TOP 5 LoginID
           , HireDate
           , DATEADD(year,5,HireDate) as DateOfVesting
FROM HumanResources.Employee
ORDER BY HireDate;

You can see that I called the DATEADD functions with the parameter values: “year”, “5”, and “HireDate”.    The “year” parameter identifies the datepart unit that will be used when the DATEADD function calculates the new date.  The parameter value of “5” indicates the number of datepart units, in this case years, that needs to be added to the “HireDate” to calculate the DateofVesting date.    When the code in Listing 3 is run, the results in Figure 5 are produced.

Figure 5: Output when running the code in Listing 3

String Function Example

Another subcategory of scalar functions is string functions.  String functions are useful for parsing, concatenating and manipulating string values.  There are lots of different built-in string functions available in SQL Server.  Some functions are used to take apart strings, others put string values together, while others are used to probe into string values.  A complete list of all the different string functions can be found in the this Microsoft documentation.  To get a feel for how to use string functions to accomplish some TSQL programming situations, let me show you an example of how to parse apart a string into its pieces.

Parsing apart a String Value

Suppose you have a string that contains a name-value pair, where the name and the value are separate by a comma. This example uses the string “FirstName, Greg”.  Let me show you how to use a few different string functions to parse this string into two different local variables (@Name, and @Value) using the code in Listing 4.

Listing 4: Code to parse a string

DECLARE @String char (15) = 'FirstName, Greg';
DECLARE @Name char(9);
DECLARE @Value varchar(4);
SELECT @Name = SUBSTRING(@String,1,CHARINDEX(', ',@String)-1),
       @Value = SUBSTRING(@String,CHARINDEX(', ',@String)+2,LEN(@String));
SELECT @Name AS [Name], @Value AS [Value];

The code in Listing 4 uses the following string functions: SUBSTRING,CHARINDEX, and LEN.

The SUBSTRING function is used to parse the @String variable into the name (@Name), and value (@Value) pieces.  The first reference to the SUBSTRING function parses the @String value into the @Name piece.  I know that the name piece starts in character 1 of the @String variable and ends just before the comma. To identify the location of the comma, I use the CHARINDEX function.  This function is used to identify the offset location within the @String variable where the string “, “ is found.   More information about the CHARINDEX function can be found here.

The second reference to the SUBSTRING function is used to populate the @Value variable from the @String variable.  The CHARINDEX function in this second reference is used to identify the starting location of the value portion of the name/value string.  Just like before, I use the comma to identify this starting offset location, but, in addition, I need to identify the ending location for the value portion.  To find the ending location, I just need to know the length of @String variable value.  To do that I use the LEN function.  More information about the LEN function can be found in the Microsoft Documentation located here.

When I run the code in Listing 4, I get the output shown in Figure 6.

Figure 6: Output from executing code in Listing 4

There are lots of different string functions that you can use to parse, build and manipulate a string.  I suggest your review Microsoft Documentation identified in this section to get a better understanding of how string functions can be used to support your TSQL application.

Conversion Function Example

There are times when you might need to convert an expression into an appropriate date type so further operations can be performed on it.  SQL Server has the CAST and CONVERT functions to perform conversions.  To better understand how each of these functions can be used, let me show you an example that uses both of these conversion functions.  But first, let’s review the syntax for the CAST and CONVERT functions found in this Microsoft documentation in Figure 7.

Figure 7: Syntax for CAST and CONVERT functions

Note that the two functions are similar, but the CONVERT function also supports an additional style parameter.  The style parameter is to help identify if any translations might need to be performed when expressions are converted, like how a floating point number should be represented, or does a date contain the century value or not.

For my first example, I will run the code in Listing 5.

Listing 5: Using CAST and CONVERT function

USE AdventureWorks2017;
GO
DECLARE @String varchar(3) = '123';
DECLARE @CurrentDateTime DATETIME = SYSDATETIME();
SELECT CAST (@String as INT) + 1 AS ConvertedString
      ,@CurrentDateTime as CurrentDateTime
      ,CONVERT (varchar,@CurrentDateTime, 103) AS OrderDate
      ,CONVERT(TIME,@CurrentDateTime,114) AS Time24HourFormat;

If you review the code in Listing 5, you will see that it uses the CAST function to convert the string value “123” into an integer value so integer math could be performed. Additionally, the code passes the to the CONVERT function twice.  The first CONVERT function call converts the @CurrentDateTime value into a VARCHAR data type value.  The style format used (103) tell the function to make sure that the @CurrentDateTime value is outputted in dd/mm/yyyy format.  The second CONVERT function call is used to covert the @CurrentDateTime value into a TIME data type, where the style parameter tells SQL Server to display the time value using a 24-hour format. When I run the code in Listing 5, I get output results in Figure 8.

Figure 8: Output when running code in Listing 5

I’d suggest you review the documentation to see what other style formats can be used to produce date values in other formats.  The CAST and CONVERT functions are helpful in formatting your data however you need it formatted and displayed.

Using Scalar Function to Support Application Requirements

Scalar functions provide lots of different methods for managing and manipulating your SQL Server data.  In this chapter, I was only able to demo a few of the commonly used scalar functions. I would suggest you spend some additional research time exploring all the available built-in function and how to use them.  Understanding what built-in functions are available out of the box will go a long way to helping you better understand built-in functions and how they can support your application coding requirements.