The following functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
DAY(date): Returns an integer representing the DAY of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.
SELECT DATEADD(dd, 30, GETDATE()) –Adds 30 days to GETDATE().
SELECT DATEADD(mm, 16, GETDATE()) –Adds 16 months to GETDATE().
ISNUMERIC( expression ): Determines whether an expression is a valid numeric type. If it is numeric it returns 1 else return 0.
SELECT ISNUMERIC(100) OUTPUT: 1
SELECT ISNUMERIC(‘100’) OUTPUT: 1
SELECT ISNUMERIC(‘100A’) OUTPUT: 0
ISDATE (expression): Determines whether an input expression is a valid date or not. If it is a valid date it returns 1 else return 0. Valid date in the sense the expression, which is present in mm/dd/yy format.
SELECT ISDATE(’12/21/98′) OUTPUT: 1
SELECT ISDATE(’21/12/98′) OUTPUT: 0
ISNULL (expression1, expression2): if expression1 is null then it returns expression2.
SELECT ISNULL(100, 200) OUTPUT: 100
SELECT ISNULL(NULL, 200) OUTPUT: 200
SELECT EMPNO, ENAME, SAL, COMM, SAL + COMM AS [TOTAL SAL] FROM EMP
-In above case if any of the value in the comm. Is null it returns null in the Total Sal because any arithmetic operations performed on a null value results to null only at this time the statement has to be written as following:
SELECT EMPNO, ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS [TOTAL SAL] FROM EMP
COALESCE (expression1, expression2, …… expression n): Returns the first not null expression in the list of expressions given, similar to isnull but we can give multiple values here.
SELECT COALESCE(NULL, 100, NULL, 200) OUTPUT: 100
SELECT EMPNO, ENAME, SAL, COMM, SAL + COALESCE(COMM, 0) AS [TOTAL SAL] FROM EMP
DATALENGTH (expression) : Returns the number of bytes used to represent any expression.
SELECT DATALENGTH(100) OUTPUT: 4
SELECT DATALENGTH(‘HELLO’) OUTPUT: 5
HOST_NAME(): Returns the name of the workstation.
SELECT HOST_NAME()
IDENT_CURRENT(‘table_name’): Returns the last identity value generated for a specified table by the identity function.
SELECT IDENT_CURRENT(‘BANK’)
IDENT_SEED(‘table_name’): Returns the seed value that was specified when the identity function in a table was created.
SELECT IDENT_SEED(‘BANK’)
IDENT_INCR(‘table_name’): Returns the increment value that was specified when the identity function in a table was created.
SELECT IDENT_INCR(‘BANK’)
NEWID( ): Creates a unique value of type uniqueidentifier.
SELECT NEWID()
NULLIF(expression1, expression2): Returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, returns a null value.
SELECT NULLIF(100, 200) OUTPUT: 100
SELECT NULLIF(100, 100) OUTPUT: NULL
ROWCOUNT_BIG(): Returns the number of rows affected by the last statement executed. If we use this after a select statement it will return us the number of rows the select statement has returned.
SELECT * FROM EMP
SELECT ROWCOUNT_BIG FROM EMP
APP_NAME(): Returns the name of the application from where the statement is executed.
SELECT APP_NAME()
CASE: Evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats:
-The simple CASE function compares an expression to a set of simple expressions to determine the result.
-The searched CASE function evaluates a set of Boolean expressions to determine the result.
- Both formats support an optional ELSE argument.
CASE <expression>
WHEN when_expression THEN result_expression
WHEN when_expression THEN result_expression
…………………………
ELSE else_result_expression
END
-In this case if the expression matches with any of the when_expression it returns the corresponding result_expression, if it does not match with any then it returns else_result_exression.
SELECT EMPNO, ENAME, SAL, JOB,
(CASE JOB
WHEN ‘PRESIDENT’ THEN ‘BIG BOSS’
WHEN ‘MANAGER’ THEN ‘BOSS’
WHEN ‘ANALYST’ THEN ‘SCIENTIST’
ELSE ‘EMPLOYEE’
END) AS COMMENTS FROM EMP
SELECT EMPNO, ENAME, JOB, SAL,
(CASE SIGN(SAL-3000)
WHEN 1 THEN ‘ABOVE TARGET’
WHEN 0 THEN ‘ON TARGET’
WHEN –1 THEN ‘BELOW TARGET’
END) AS COMMENTS FROM EMP
-The above statement can be written in one more way also by using the second format of the CASE function.
CASE
WHEN condition THEN result_expression
WHEN condition THEN result_expression
…………………………
ELSE else_result_expression
END
SELECT EMPNO, ENAME, JOB, SAL,
(CASE
WHEN SAL>3000 THEN ‘ABOVE TARGET’
WHEN SAL=3000 THEN ‘ON TARGET’
WHEN SAL<3000 THEN ‘BELOW TARGET’
END) AS COMMENTS FROM EMP
Set Operators:
COUNT(expression): Returns the number of items in a group.
SELECT COUNT(*) FROM EMP
SELECT COUNT(*) FROM EMP WHERE DEPTNO=20
SELECT COUNT(COMM) FROM EMP
COUNT_BIG(expression): COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.
SELECT COUNT_BIG(*) FROM EMP
SUM(expression): Returns the sum of all the values. SUM can be used with numeric columns only. Null values are ignored
SELECT SUM(SAL) FROM EMP
AVG(expression): Returns the average of the values in a group. Null values are ignored.
SELECT AVG(SAL) FROM EMP
MAX(expression): Returns the maximum value in the expression.
SELECT MAX(SAL) FROM EMP
MIN(expression): Returns the minimum value in the expression.
SELECT MIN(SAL) FROM EMP
STDEV(expression): Returns the statistical standard deviation of all values in the specified expression.
SELECT STDEV(SAL) FROM EMP
