to the specified value n. Example: SELECT FLOOR(4.88) = 4 LOG(n) Calculates the natural (i.e., base e) logarithm of n. Examples: SELECT LOG(4.67) = 1.54 SELECT LOG(0.12) = –2.12 LOG10(n) Calculates the logarithm (base 10) for n. Examples: SELECT LOG10(4.67) = 0.67 SELECT LOG10(0.12) = PI() Returns the value of the number pi (3.14). POWER(x,y) Calculates the x^y. Examples: SELECT POWER(3.12,5) = 295.65 SELECT POWER(81,0.5) = 9 RADIANS(n) Converts degrees to radians. Examples: SELECT RADIANS(90.0) = 1.57 SELECT RADIANS(42.97) = 0.75 RAND Returns a random number between 0 and 1 with a FLOAT data type.
p,[t]) Rounds the value of the number n by using the precision p. Use positive values of p to round on the right side of the decimal point and use negative values to round on the left side. An optional parameter t causes n to be truncated. Examples: SELECT ROUND(5.4567,3) = 5.4570 SELECT = 350.0000 SELECT = 340.0000 ROWCOUNT_BIG Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system. The return of this function has the BIGINT data type. SIGN(n) Returns the sign of the value n as a number (+1 for positive, –1 for negative, and 0 for zero). Example: SELECT SIGN(0.88) = 1 SIN(n) Calculates the sine of n. n and the resulting value
belong to the FLOAT data type. SQRT(n) Calculates the square root of n. Example: SELECT SQRT(9) = 3 SQUARE(n) Returns the square of the given expression. Example: SELECT SQUARE(9) = 81 TAN(n) Calculates the tangent of n. n and the resulting value belong to the FLOAT data type. Date functions calculate the respective date or time portion of an expression or return the value from a time interval. Transact-SQL supports the following date functions: Function Explanation GETDATE() Returns the current system date and time. Example: SELECT GETDATE() = 2008-01-01 13:03:31.390 Returns the specified part item of a date date as an integer. Examples: SELECT DATEPART(month, '01.01.2005') = 1 (1 = January) SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday) DATENAME(item, date) Returns the specified part item of the date date as a character string. Example: SELECT DATENAME(weekday, '01.01.2005') = Saturday Calculates the difference between the two date parts dat1 and dat2 and returns the result as an integer in units specified by the value item. Example: SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age of each employee.
RIGHT(z,length) Returns the last length characters from the string z. Example: SELECT RIGHT('Notebook',4) = 'book' RTRIM(z) Removes trailing blanks of the string z. Example: SELECT RTRIM('Notebook ') = 'Notebook' SOUNDEX(a) Returns a four-character SOUNDEX code to determine the similarity between two strings. Example: SELECT SOUNDEX('spelling') = S145 Returns a string with spaces of length specified by length. Example: SELECT SPACE = ' ' STR(f,[len [,d]]) Converts the specified float expression f into a string. len is the length of the string including decimal point, sign, digits, and spaces (10 by default), and d is the number of digits to the right of the decimal point to be returned. Example: SELECT STR(3.45678,4,2) = '3.46' STUFF(z1,a,length,z2) Replaces the partial string z1 with the partial string z2 starting at position a, replacing length characters of z1. Examples: SELECT STUFF('Notebook',5,0, ' in a ') = 'Note in a book' SELECT STUFF('Notebook',1,4, 'Hand') = 'Handbook' SUBSTRING(z,a,length) Creates a partial string from string z starting at the position a with a length of length.