FUNCTIONS
AGGREGATE (STATISTICAL) FUNCTIONS
AVG(numeric expression)Returns numeric expression (integer, decimal, money, or float)- Returns the computed average of the values in a group. NULL values are ignored.
BINARY_CHECKSUM(row or column expression)Returns numeric expression.- Returns the binary checksum value computed over a row or column of a table.
CHECKSUM(expression)Returns integer expression.- Returns a checksum computed over a row of a table or over a list of expressions.
CHECKSUM_AGG(group expression)Returns integer expression.- Returns a checksum computed from the values of the group expression.
COUNT(group expression)Returns integer expression.- Returns the number of items (rows) in the specified group. Null values are ignored.
COUNT_BIG(group expression)Returns bigint expression.- Returns the number of items (rows) in the specified group. Null values are ignored.
GROUPING(column name as nvarchar(128))Returns integer expression.- An aggregate function that causes an extra column to be output with a value of 1 when the row was added by either the CUBE or ROLLUP operator and a 0 when not.
MAX(expression)Returns same type as the specified expression.- Returns the maximum value for the specified expression.
MIN(expression)Returns same type as the specified expression.- Returns the minimum value for the specified expression.
STDEV(expression)Returns float expression.- Returns the statistical standard deviation of all values returned in the specified expression.
STDEVP(expression)Returns float expression.- Returns the statistical standard deviation for the population of all values returned in the specified expression.
SUM(expression)Returns numeric expression.- Returns the sum of all values returned in the specified expression. When DISTINCT is specified, returns the sum of all distinct values only.
VAR(expression)Returns float expression.- Returns the statistical variance of all values returned in the specified expression.
VARP(expression)Returns float expression.- Returns the statistical variance for the population of all values returned in the specified expression. Also returns the current value of the SET DATEFIRST parameter which indicates the specified first day of each week.
DATE FUNCTIONS
CONVERT(datatype, expression, datestyle)Returns expression of the specified type.- Returns expression converted to the specified datatype styled as specified.
DATEADD(datepart, increment expression, datetime expression)Returns datetime expression.- Returns a new datetime expression based on adding an increment interval to the specified date. See table 1 for dateparts.
DATEDIFF(datepart, start datetime expression, end datetime expression)Returns datetime expression.- Returns the number of date and time boundaries crossed two specified dates.
DATENAME(datepart, datetime expression)Returns varchar expression.- Returns a character expression representing the specified datetime part of the specified date.
DATEPART(datepart, datetime expression)Returns integer expression.- Returns an integer repesenting the specified datepart of the specified expression.
DAY(datetime expression)Returns integer expression.- Returns an integer expression representing the day daypart of the specified expression.
GETDATE()Returns datetime expression.- Returns the current system date and time.
GETUTCDATE()Returns datetime expression.- Returns a datetime value representing the current UTC time.
MONTH(datetime expression)Returns integer expression.- Returns an integer representing the month daypart.
YEAR(datetime expression)Returns integer expression.- Returns an integer representing the year daypart.
| Interval | Datepart |
|---|---|
| Year | YY, YYYY |
| Quarter | Q,QQ |
| Month | M, MM |
| Day of year | Y, DY |
| Day of week | W,DW |
| Day | D, DD |
| Week | WK, WW |
| Hour | HH |
| Minute | N, MI |
| Second | S, SS |
| Millisecond | MS |
| Microsecond* | MCS |
| Nanosecond* | NS |
* not available prior to SQL2008
| Date Style | Standard | Format | Example |
|---|---|---|---|
| 0 | Default | MMM DD YYYY HH:MMPM | Feb 11 2011 8:17PM |
| 1 | US | MM/DD/YY | 02/11/11 |
| 2 | ANSI | MM.DD.YY | 11.02.11 |
| 3 | UK/FR | DD/MM/YY | 15/02/11 |
| 4 | DE | DD.MM.YY | 15.02.11 |
| 5 | IT | DD-MM-YY | 16-02-11 |
| 6 | DD MMM YY | 15 Feb 11 | |
| 7 | MMM DD, YY | Feb 11, 11 | |
| 8 | HH MM SS | 21:08:26 | |
| 9 | MMM DD YYYY HH:MM:SS | Feb 11 2011 9:23:07 | |
| 10 | USA | MM DD YY | 02-11-11 |
| 100 | Default | MMM DD YYYY HH:MMPM | Feb 11 2011 8:17PM |
| 101 | US | MM/DD/YYYY | 02/11/2011 |
| 102 | ANSI | MM.DD.YYYY | 11.02.2011 |
| 103 | UK/FR | DD/MM/YYYY | 15/02/2011 |
| 104 | DE | DD.MM.YYYY | 15.02.2011 |
| 105 | IT | DD-MM-YYYY | 16-02-2011 |
| 106 | DD MMM YYYY | 15 Feb 2011 | |
| 107 | MMM DD, YYYY | Feb 11, 2011 | |
| 108 | HH MM SS | 21:08:26 | |
| 109 | MMM DD YYYY HH:MM:SS | Feb 11 2011 9:23:07 | |
| 110 | USA | MM DD YYYY | 02-11-2011 |
| 126 | ISO8601 | YYYY-MM-DDTHH:MM:SS.mmm | 2011-02-11T23:24:35.000 |
TRIGNOMETRIC FUNCTIONS
ACOS(radians expression (float))Returns float expression.- Returns the angle in radians whose cosign is the is the given float expression.
ASIN(radians expression (float))Returns float expression.- Returns the angle in radians whose sin is the given float expression.
ATAN(radians expression (float))Returns float expression.- Returns the angle in radians whose tangent is the given float expression.
ATAN2(radians expression, radians expression)Returns float expression.- Returns the angle in radians whose tangent is the quotient of two given float expressions.
COS(radians expression)Returns float expression.- Returns the trignometric cosine of the given angle.
COT(radians expression)Returns float expression.- Returns the trignometric cotangent of the given angle.
DEGREES(radians expression)Returns degrees expression.- Returns angle in degrees from given angle in radians.
EXP(radians expression)Returns float expression.- Returns the exponential value of the given expression.
LOG(radians expression)Returns float expression.- Returns the natural logarithm of the given expression.
LOG10(radians expression)Returns float expression.- Returns the base 10 logarithm of the given expression.
RADIANS(degrees expression)Returns radians expression.- Returns radians value corresponding to the given degrees expression.
SIN(radians expression)Returns float expression.- Returns the trignometric sine of the given angle.
TAN(radians expression)Returns float expression.- Returns the trignometric tangent of the given angle.
Figure 3
Radians to Degrees Conversion
MATH FUNCTIONS
ABS(numeric expression)Returns same type as expression.- Returns the absolute positve value of the given numeric expression.
CEILING(numeric expression)Returns same type as expression.- Returns the smallest integer value greater than or equal to the given expression.
FLOOR(numeric expression)Returns same type as expression.- Returns the largest intever value less than or equal to the given expression.
PI()Returns float expression.- Returns value of PI (3.14159)
POWER(numeric expression, power)Returns same type as expression.- Returns the value of the given expression raised to the specified power.
RAND(optional seed value)Returns float expression.- Returns a random float value ranging between 0 and 1.
ROUND(numeric expression, precision, opt. function)Returns same type as expression.SIGN(numeric expression)Returns numeric expression.- Returns the positive (+1), zero(0), or negative(-1) sign of the given expression in the same type.
SQRT(numerical expression)Returns float expression.- Returns the square root of the given expression.
SQUARE(numeric expression)Returns float expression.- Returns the square of the given expression.
STRING FUNCTIONS
ASCII(character expression)Returns integer expression.- Returns the ascii code value of the left most character in the given expression.
CHAR(tinyint expression)Returns a single character.- Converts given integer ascii code to a character.
CHARINDEX(search expression, expression to be searched, start location)Returns integer expression.- Returns the starting position of the search expression within the given expression.
DIFFERENCE(soundex expression, soundex expression)Returns integer expression.- Returns the difference between the soundex values of two character expressions as an integer.
LEFT(character expression, number of characters)Returns character expression.- Returns the left most specified number of characters from the give character expression.
LEN(character expression)Returns integer expression.- Returns the number of characters in a specified character expression.
LOWER(character expression)Returns character expression.- Returns a character expression with all upper case characters converted to lower case.
LTRIM(character expression)Returns character expression.- Returns a character expression with all leading spaces removed.
NCHAR(integer expression)Returns unicode expression.- Returns the unicode expression corresponding to the given integer.
PATINDEX(search pattern literal, character expression)Returns integer expression.- Returns the starting position of the first occurrance of a pattern in a given character expression.
QUOTENAME(character expression, optional quote character)Returns character expression.- Returns a unicode expression with delimiters added to make a SQL valid delimited identifier.
REPLACE(character expression to be searched, search expression, replacement expression)Returns character expression.- Replaces all occurances of the second epression with the third expression in the first expression.
REPLICATE(character expression, integer expression)Returns character expression.- Returns given character expression the specified number of times.
REVERSE(character expression)Returns character expression.- Returns the given character expression in reverse order, last character first.
RIGHT(character expression, integer expression)Returns character expression.- Returns the specified number of characters from the right end of given character expression.
RTRIM(character expression)Returns character expression.- Returns the specified character expression with all trailing spaces removed.
SOUNDEX(character expression)Returns soundex expression.- Returns a four character soundex expression for the given character expression used for phonetic comparisons.
SPACE(integer expression)Returns character expression.- Returns a character expression consisting of a string of space characters.
STR(float expression, optional length expression, optional scale expression)Returns character expression.- Returns a character expression converted from a numeric expression.
STUFF(searched character expression, start position, number of characters, replacement expression)Returns character expression.- Deletes a specified number of characters starting at the designated position, then inserts the replacement expression.
SUBSTRING(character expression, start position, length)Returns character expression.- Returns specified portion of a given character expression.
UNICODE(character expression)Returns integer expression.- Returns the unicode value of the first character of the given character expression.
UPPER(character expression)Returns character expression.- Returns the given character expression with all lower case character converted to upper case.
MISCELLANEOUS FUNCTIONS
CAST(expression, datatype)Returns expression cast to the specified type.- Explicitly converts an expression of one type to the specified type. Cast is SQL-92 compliant.
COALESCE(any number of parameters or expression of any type)Returns expression of same type.- Returns the first non null parameter or expression. If all parameters/expressions are null, returns null.
CONVERT(datatype, expression)Returns expression of the specified type.- Returns expression converted to the specified datatype. Convert is not SQL-92 compliant.
DATALENGTH(expression)Returns integer expression.- Returns number of bytes consumed by the expression.
ISDATE(expression)Returns integer expression.- Returns 1 if the expression is or can convert into a date, 0 if not.
ISNULL(check expression, replacement expression)Returns expression of same type.- Returns check expression if not null, or replacement expression if is null.
ISNUMERIC(expression)Returns integer expression.- Returns 1 if the expression is or can convert into a number, 0 if not.