Functions

Part:   1  2  

(Continued from previous part...)

Numeric Functions

"ABS(number)" - Returns the absolute value of the specified number.

"CEILING(number)" - Returns the smallest integer value not less than the specified number.

"COS(number)" - Returns the cosine of the specified number.

"PI()" - Returns the value of PI, 3.141592653589793116.

"EXP(number)" - Returns the value of e raise to the power of the specified number.

"FLOOR(number)" - Returns the largest integer value not greater than the specified number.

"GREATEST(list)" - Returns the largest value of the specified list of values.

"LEAST(list)" - Returns the smallest value of the specified list of values.

"RAND()" - Returns a random number between 0 and 1.

"ROUND(number)" - Returns the nearest integer of the specified number.

"ROUND(number, position)" - Returns the rounded value of the specified number at the specified decimal position.

"SIGN(number)" - Returns -1, 0 or 1 if the specified number is negative, zero or positive.

"TRUNCATE(number, position)" - Returns the truncated value of the specified number at the specified decimal position.

Examples of numeric functions, NumericFunctions.sql:

-- NumericFunctions.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT PI();
SELECT CEILING(PI());
SELECT FLOOR(PI());
SELECT ROUND(PI(),4);
SELECT TRUNCATE(PI(),4);

Output:

PI()
3.141593
CEILING(PI())
4
FLOOR(PI())
3
ROUND(PI(),4)
3.1416
TRUNCATE(PI(),4)
3.1415

Date and Time Functions

"CURRENT_DATE()" - Returns the current date.

"CURRENT_TIME()" - Returns the current time.

"CURRENT_TIMESTAMP()" - Returns the current date and time.

"EXTRACT(field_name FROM date_time)" - Returns a numeric value of the specified field name of the specified date and time value.

Examples of date and time functions, DateTimeFunctions.sql:

-- DateTimeFunctions.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT CURRENT_TIME();
SELECT EXTRACT(HOUR FROM CURRENT_TIME());
SELECT EXTRACT(MONTH FROM TIMESTAMP'1999-02-28 10:20:30');

Output:

CURRENT_TIME()
21:38:37
EXTRACT(HOUR FROM CURRENT_TIME())
21
EXTRACT(MONTH FROM TIMESTAMP'1999-02-28 10:20:30')
2

Cast Function

"CAST(value AS type)" - Returns the casted value of the specified value as the specified type.

Examples of cast function, CastFunctions.sql:

-- CastFunctions.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT PI() = '3.141593';
SELECT CAST(PI() AS CHAR) = '3.141593';
SELECT SUBSTRING(CAST(TIME'10:20:30' AS CHAR),4,2);
SELECT CAST(SUBSTRING('JAN 10, 1999',5,2) AS SIGNED) + 1;

Output:

PI() = '3.141593'
0
CAST(PI() AS CHAR) = '3.141593'
1
SUBSTRING(CAST(TIME'10:20:30' AS CHAR),4,2)
20
CAST(SUBSTRING('JAN 10, 1999',5,2) AS SIGNED) + 1
11

Part:   1  2