| Aside from using operators to manually construct expressions, SQL provides built-in functions that you can use. TIP Functions are small blocks of code that can perform operations and return a value.
Functions are available simply by making a call to them and passing the value and/or values that you want it to operate on. Date and Time Functions Date and Time functions allow for manipulations using dates and times that are stored within your database. The following code: Select * From Transactions Where DatePurchased Like '6/30/2002' Would produce the following results: | TransactionID | ProductID | CustomerID | DatePurchased | | 24 | 3 | 2 | 6/30/02 | If you wanted to find all the transactions from the previous month, you could use the DateAdd function: Select * From Transactions Where DatePurchased > DateAdd(m, -1, Date()) Assuming that the current date was 6/30/02, the results would be | TransactionID | ProductID | CustomerID | DatePurchased | | 24 | 3 | 2 | 6/15/02 | | 15 | 26 | 2 | 6/3/02 | | 3 | 17 | 15 | 6/2/02 | You will also notice that the DateAdd function accepts parameters. TIP Parameters are values that you pass into the function so that it knows what to do or how to return the value.
These parameters include the following: How much time will be added to the date. Typically, you would want to use one of three values: m for month, w for week, and d for day. How much time to add or subtract in this case, one month will be subtracted. The date that you want to use. In this case, we are calling another function the system date. By using date(), you are effectively reading the date and time from the computer and passing it in as a value. There are many other Date and Time functions. Too many, in fact, to cover in this small section. Date and Time functions are among the widely used functions in SQL and are worth the research. CAUTION If you are using a database other the SQL Server or Access, the function names that you use may differ. For instance, the DateAdd function is a Microsoft database function. Check with your documentation to determine the functions that are available with your database.
The Count Function One of the most obvious functions available is the Count function. The Count function is used when a count of records needs to be performed. Consider the following table of data from the orders table: | CustomerID | OrderNumber | ProductID | Quantity | | 24 | 33423325 | 2 | 4 | | 15 | 26234556 | 2 | 2 | | 3 | 17456326 | 15 | 7 | Use the following to count the amount of orders that you have taken in a day from the orders table: Select Count(Quantity) As NumberOfPayments From Orders The statement would result in the following: NumberOfPayments 3 Notice that you pass in the field name as a parameter in the Count function. The parameter is evaluated and a value is returned into a virtual field named NumberOfPayments. The Sum Function Unlike the Count function that returns a value from a calculation on the number of fields, the Sum function performs a calculation on data within those fields. If, for instance, you needed to know the total number of items you sold, you could modify the statement to read: Select Sum(Quantity) As Total From Orders The statement would produce the following results: Total 13 Rather than simply doing a count on the records, the sum is calculated based on the values within them. The Avg Function The Avg function returns the average of values within specific fields. If you modified the statement to read: Select Avg(Quantity) As Average From Orders The following would result: Total 4.333 The Min and Max Functions The Min and Max functions enable you to find the smallest and largest values of a specific record. To get the minimum quantity ordered, you could write Select Min(Quantity) As Minimum From Orders which would produce Minimum 2 To receive the maximum value of a record in the database, try Select Max(Quantity) As Maximum From Orders which would produce Maximum 7 Arithmetic Functions Aside from using Sum, Min, Max, and Avg, a few other arithmetic functions can help you when calculating fields within your database. They are as follows: ABS Returns the absolute value. CEIL Returns the smallest integer value not greater than the value. FLOOR Returns the largest integer value not greater than the value. COS Returns the cosine of the value where the value is the radians. COSH Returns the hyperbolic cosine of the value where the value is the radians. SIN Returns the sine of the value where the value is the radians. SINH Returns the hyperbolic sine of the value where the value is the radians. TAN Returns the tangent of the value where the value is the radians. TANH Returns the hyperbolic tangent of the value where the value is the radians. EXP Returns the mathematical constant e by the provided value. MOD Returns the remainder of a value divided by a second. SIGN Returns the sign of the argument as 1, 0, or 1, depending on whether the value is negative, zero, or positive. SQRT Returns the non-negative square root of a value. POWER Returns the result of a value raised to the power of a second value. LN Returns the natural logarithm of a value. LOG Returns the logarithm of a value in the base of a second value. String Functions String functions are similar to other functions, except they work with literal text values rather than numerical values. CHR Converts an ASCII value to its string equivalent. CONCAT Concatenates (merges) two string into one. INITCAP Returns the first letter of each word capitalized. UPPER Returns the value in all uppercase. LOWER Returns the value in all lowercase. LPAD Returns a value padded on the left based on the numerical value you specify. RPAD Returns a value padded on the right based on the numerical value you specify. LTRIM Returns a value with the leftmost characters that match the number of characters that you specify omitted. RTRIM Returns a value with the rightmost characters that match the number of characters that you specify omitted. REPLACE Use the Replace function to change a portion of the string with a value that you specify. Replace takes three values (string, target, replacement string). SUBSTR Returns the substring of a value that begins at pos and is ten characters long. Substr takes three values (string, position, length). LENGTH Returns length of string in characters. | 
