| Executive Summary: T-SQL provides three types of built-in functions: aggregate, rowset, and scalar. This lesson explores the T-SQL scalar functions, which take a single value as input and return a single value as output. You'll learn how to use the GETDATE, DATENAME, and DATEADD functions to work with dates as well as how to use the LEFT, RIGHT, SUBSTRING, and REPLACE functions to manipulate strings. |
T-SQL has many built-in functions that come in four basic flavors: aggregate, ranking, rowset, and scalar. You’ve already been introduced to aggregate functions, which take in sets of data and return a single aggregated value. Because I covered several aggregate functions in “T-SQL 101, Lesson 3,” May 2008, InstantDoc ID 98315, I won’t cover them here.
Ranking functions are used to determine the particular rank of a record in a portion of a result set. Rowset functions generate a result set that can be referenced in T-SQL statements as if it were a table. Due to the complex nature of the ranking and rowset functions, they’re beyond the scope of this lesson. If you’d like to find out more about them, see the SQL Server 2005 Books Online (BOL) documentation at msdn.microsoft.com/en-us/library/ms189798.aspx for ranking functions and msdn.microsoft.com/en-us/library/ms187957.aspx for rowset functions.
Scalar functions can accept one or more input parameters but return only a single value as output. You can use scalar functions in any valid expression, including another scalar function, so you can use them to perform a wide variety of operations. You can use scalar functions for converting dates, truncating strings, and even performing advanced mathematical operations that involve trigonometry. In this lesson, I’ll introduce you to several common scalar functions used for working with dates and manipulating strings. I’ll leave the trigonometric functions for a much later lesson.
The Prerequisites
To follow along with the examples in this lesson, make sure your MyDB database contains the following:
• The Employee table created in Lesson 3
• The vMovieReviewSummary view created in Lesson 7
If you haven’t created this table and view, you’ll find the code to do so in the 99832.zip file. To download this file, go to www.sqlmag.com, enter 99832 in the InstantDoc ID text box, and click the 99832.zip hotlink.
Functions for Working with Dates
DBAs commonly work with dates, so let’s start by examining three date-related functions: GETDATE, DATENAME, and DATEADD.
GETDATE. In T-SQL, you can use the GETDATE function to obtain the current system date and time. Although GETDATE doesn’t have any input parameters, you still need to include the parentheses in your code because that’s how SQL Server typically identifies functions. To use this function, you include it in a query statement such as
SELECT GETDATE() AS 'Current Date/Time'
Because you’re accessing the GETDATE function from within a SELECT statement, the result is treated as if it were a column being retrieved from a table, like this
Current Date/Time
2008-05-31 22:20:52.917
DATENAME. Now let’s say you want to determine on what day of the week a particular date will occur. You could write some complex calendar code, or you could use the DATENAME function. As the following syntax shows, DATENAME requires two input parameters:
DATENAME(datepart, date)
The datepart parameter specifies the part of the date to retrieve, and the date parameter specifies the date. For example, to determine on what day of the week New Year’s Eve occurs this year, you’d execute the code
SELECT DATENAME(dw,'2008-12-31')
AS 'New Year''s Eve'
which would give you the result
New Year's Eve
Wednesday
Continue on Page 2