Introduction
SQL provides several built-in scalar functions. Each scalar function takes one value as input and returns one value as output for each row in a result set.
You use scalar functions wherever an expression is allowed within a T-SQL statement.
Syntax
- CAST ( expression AS data_type [ ( length ) ] )
- CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
- PARSE ( string_value AS data_type [ USING culture ] )
- DATENAME ( datepart , date )
- GETDATE ( )
- DATEDIFF ( datepart , startdate , enddate )
- DATEADD (datepart , number , date )
- CHOOSE ( index, val_1, val_2 [, val_n ] )
- IIF ( boolean_expression, true_value, false_value )
- SIGN ( numeric_expression )
- POWER ( float_expression , y )
Remarks
Scalar or Single-Row functions are used to operate each row of data in the result set, as opposed to aggregate functions which operate on the entire result set.
There are ten types of scalar functions.
- Configuration functions provide information about the configuration of the current SQL instance.
- Conversion functions convert data into the correct data type for a given operation. For example, these types of functions can reformat information by converting a string to a date or number to allow two different types to be compared.