一、內(nèi)部函數(shù)
　 1、內(nèi)部合計(jì)函數(shù)
　　　 1）COUNT（*）　　　　　　　　　　返回行數(shù)
　　　 2）COUNT（DISTINCT COLNAME）　　 返回指定列中唯一值的個(gè)數(shù)
　　　 3）SUM（COLNAME/EXPRESSION）　　 返回指定列或表達(dá)式的數(shù)值和；
　　　 4）SUM（DISTINCT COLNAME）　　　 返回指定列中唯一值的和
　　　 5）AVG（COLNAME/EXPRESSION）　　 返回指定列或表達(dá)式中的數(shù)值平均值
　　　 6）AVG（DISTINCT COLNAME）　　　 返回指定列中唯一值的平均值
　　　 7）MIN（COLNAME/EXPRESSION）　　 返回指定列或表達(dá)式中的數(shù)值最小值
　　　 8）MAX（COLNAME/EXPRESSION）　　 返回指定列或表達(dá)式中的數(shù)值最大值
　 2、日期與時(shí)間函數(shù)
　　　 1）DAY（DATE/DATETIME EXPRESSION）　　　返回指定表達(dá)式中的當(dāng)月幾號(hào)
　　　 2）MONTH（DATE/DATETIME EXPRESSION）　　返回指定表達(dá)式中的月份
　　　 3）YEAR（DATE/DATETIME EXPRESSION）　　 返回指定表達(dá)式中的年份
　　　 4）WEEKDAY（DATE/DATETIME EXPRESSION）　返回指定表達(dá)式中的當(dāng)周星期幾
　　　 5）DATE（NOT DATE EXPRESSION）　　　　　返回指定表達(dá)式代表的日期值
　　　 6）TODAY　　　　　　　　　　　　　　　　返回當(dāng)前日期的日期值
　　　 7）CURRENT[first to last]　　　　　　　 返回當(dāng)前日期的日期時(shí)間值
　　　 8）COLNAME/EXPRESSION UNITS PRECISION　 返回指定精度的指定單位數(shù)
　　　 9）MDY（MONTH，DAY，YEAR）　　　　　　　返回標(biāo)識(shí)指定年、月、日的日期值
　　　 10）DATETIME（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表達(dá)式代表的日期時(shí)間值
　　　 11）INTERVAL（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表達(dá)式代表的時(shí)間間隔值
　　　 12）EXTEND（DATE/DATETIME EXPRESSION，[first to last]）返回經(jīng)過調(diào)整的日期或日期時(shí)間

值

　　　 例子1、和UNITS合用，指定日期或時(shí)間單位(year,month,day,hour,minute,seond,fraction)：
　　　　　　　let tmp_date = today + 3 UNITS day
　　　 例子2、let tmp_date = MDY(10,30,2002)　　-- 2002-10-30
　　　 例子3、let tmp_date = today + interval(7) day to day　--當(dāng)前時(shí)間加上7天；
　　　　　　　注：該功能與1相似；
　　　 例子4、EXTEND轉(zhuǎn)換日期或日期時(shí)間值
　　　　　　　let tmp_inthour = extend(datetime1,hour to hour)

　 3、代數(shù)函數(shù)
　　　1）ABS(COLNAME/EXPRESSION)：　　　　　　 取絕對值
　　　2）MOD（COLNAME/EXPRESSION，DIVISOR）　　返回除以除數(shù)后的模（余數(shù)）
　　　3）POW（COLNAME/EXPRESSION，EXPONENT）　 返回一個(gè)值的指數(shù)冥
　　　　 例子：let tmp_float = pow(2,3)　--8.00000000

　　　4）ROOT（COLNAME/EXPRESSION，[index]）　 返回指定列或表達(dá)式的根值

　　　5）SQRT（COLNAME/EXPRESSION）　　　　　　返回指定列或表達(dá)式的平方根值

　　　6）ROUND（COLNAME/EXPRESSION，[factor]） 返回指定列或表達(dá)式的圓整化值
　　　7）TRUNC（COLNAME/EXPRESSION，[factor]） 返回指定列或表達(dá)式的截尾值
　　　　 說明：上兩者中FACTOR指定小數(shù)位數(shù)，若不指定，則為0；若為負(fù)數(shù)，則整化到小數(shù)點(diǎn)左邊；
　　　　 注：ROUND是在指定位上進(jìn)行4舍5入；TRUNC是在指定位上直接截?cái)啵?
　　　　　let tmp_float = round(4.555,2) --4.56
　　　　　let tmp_float = trunc(4.555,2) --4.55

　 4、指數(shù)與對數(shù)函數(shù)
　　　1）EXP（COLNAME/EXPRESSION）　　　　返回指定列或表達(dá)式的指數(shù)值
　　　2）LOGN（COLNAME/EXPRESSION）　　　 返回指定列或表達(dá)式的自然對數(shù)值
　　　3）LOG10（COLNAME/EXPRESSION）　　　返回指定列或表達(dá)式的底數(shù)位10的對數(shù)值

　 5、三角函數(shù)
　　　1）COS（RADIAN EXPRESSION）　　　　　返回指定弧度表達(dá)式的余弦值
　　　2）SIN（RADIAN EXPRESSION）　　　　　正弦
　　　3）TAN（RADIAN EXPRESSION）　　　　　正切
　　　4）ACOS（RADIAN EXPRESSION）　　　　 反余弦
　　　5）ASIN（RADIAN EXPRESSION）　　　　 反正弦
　　　6）ATAN（RADIAN EXPRESSION）　　　　 反正切
　　　7）ATAN2（X，Y）　　　　　　　　　　 返回坐標(biāo)（X，Y）的極坐標(biāo)角度組件

　 6、統(tǒng)計(jì)函數(shù)
　　　1）RANGE（COLNAME）　　　 返回指定列的最大值與最小值之差 = MAX（COLNAME）-MIN

（COLNAME）
　　　2）VARIANCE（COLNAME）　　返回指定列的樣本方差；
　　　3）STDEV（COLNAME）　　　 返回指定列的標(biāo)準(zhǔn)偏差；

　 7、其他函數(shù)
　　　1）USER　　　　　　　　　　　　　　返回當(dāng)前用戶名
　　　2）HEX（COLNAME/EXPRESSION）　　　 返回指定列或表達(dá)式的十六進(jìn)制值
　　　3）LENGTH（COLNAME/EXPRESSION）　　返回指定字符列或表達(dá)式的長度
　　　4）TRIM（COLNAME/EXPRESSION）　　　刪除指定列或表達(dá)式前后的字符
　　　5）COLNAME/EXPRESSION || COLNAME/EXPRESSION　返回并在一起的字符；

二、IDS內(nèi)部函數(shù)
　 1、DBSERVERNAME　　　返回?cái)?shù)據(jù)庫服務(wù)器名　let tmp_char=DBSERVERNAME
　 2、SITENAME　　　　　返回?cái)?shù)據(jù)庫服務(wù)器名　let tmp_char=SITENAME
　　　說明：兩者功能相同；

　 3、DBINFO（‘SPECIAL_KEYWORD')　　　返回只關(guān)鍵字值
　　　例子1：返回?cái)?shù)據(jù)中每個(gè)表的DBSPACE名稱
　　　　 select dbinfo('dbspace',partnum),tabname from systables
　　　　　where tabid>99 and tabtype='T'　(OK)
　　　例子2：返回任何表中插入的最后一個(gè)SERIAL值
　　　　　select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
　　　例子3：返回最后一個(gè)SELECT，INSERT，UPDATE，DELETE或EXECUTE PROCEDURE語句處理的行數(shù)；
　　　　　select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;

?

Date Manipulation

One of the more difficult concepts in Informix's handling of date and time values concerns the use of the variables in arithmetic or relational expressions. You can add or subtract DATE and DATETIME variables from each other. You can add or subtract an INTERVAL to a DATE or DATETIME. Table 1 shows the results of different types of operations on DATE and DATETIME values.

Table 1. Operations on DATE and DATETIME Variables

Notice that it's always okay to subtract one DATE or DATETIME value from another, as shown here:

CURRENT - "07/01/1950" = INTERVAL (my age)
"12/25/2000" – CURRENT = INTERVAL (how long till Xmas?)

In such a case, the result is always an INTERVAL variable. It would make no sense to add two DATE or DATETIME values together. What could such an addition represent?

UNITS Keyword

When working with INTERVAL values, sometimes it is necessary to specify the precision with which you are dealing. For example, suppose you have the following field defined:

lead_time INTERVAL DAY to DAY

To add 10 days to the lead time you could use a SQL statement like this:

SELECT lead_time + INTERVAL(10) DAY to DAY
FROM orders

You could achieve the same results using the UNITS keyword:

SELECT lead_time + 10 UNITS DAY 
FROM orders

Like most other programming languages, SQL often allows you to achieve the same ends with different statements. Sometimes the choice is one of personal style. Other times, one format fits in better with a structured style of code writing than another format does.

Functions

There are several built-in functions that affect date and time calculations. They can apply to either DATE or DATETIME values, with some exceptions.

TODAY

The TODAY function returns a DATE data value representing the current date. For example, you could execute a SQL function like this:

UPDATE member SET change_date = TODAY
WHER member_number = 12345

CURRENT

The CURRENT function is similar to the TODAY function, except it returns a DATETIME value. Without specific qualifiers, the default is YEAR to FRACTION(3). You can change the precision by using the same YEAR to FRACTION qualifiers as you use for DATETIMES. Thus, this would be legal:

SELECT * from member
WHERE elapsed_time < CURRENT YEAR to DAY

DATE

The DATE function takes as input a non-DATE value such as CHAR, DATETIME, or INTEGER and returns the corresponding DATE value. For example, the following SQL translates a CHARACTER value to a DATE:

SELECT * from member
WHERE enrollment_date > DATE('01/01/99')

DAY

This function returns an integer representing the day of the month. Here's an example:

SELECT * from member
WHERE DAY(enrollment_date) > DAY(CURRENT)

MONTH

This performs like the DAY function except it returns an integer between 1 and 12 representing the month:

SELECT * from member
WHERE enrollment_date > MONTH('01/01/99')

WEEKDAY

This returns an integer representing the day of the week, with 0 being Sunday and 6 being Saturday:

SELECT * from member
WHERE WEEKDAY(enrollment_date) > WEEKDAY(CURRENT)

YEAR

This function is like the ones above, only it returns a four-digit integer representing the year.

EXTEND

This function allows you to use different precisions in a DATETIME than you have specified in the declaration of the variable. It uses the same FIRST to LAST syntax as the DATETIME variables. This function is used to adjust the precision of a DATETIME variable to match the precision of an INTERVAL that you are using in a calculation. If the INTERVAL value has fields that are not part of the DATETIME value that you are using in a calculation, use the EXTEND function to adjust the precision of the DATETIME. EXTEND can either increase or decrease the precision of a DATETIME, depending upon the FIRST and LAST values.

Suppose myvariable is declared as DATETIME YEAR to DAY. If you want to add or subtract an INTERVAL defined as MINUTE, you first have to extend the DATETIME as follows:

SELECT EXTEND(myvariable, YEAR to MINUTE) – 
INTERVAL(5) MINUTE to MINUTE
FROM member

The resulting value will be DATETIME YEAR to MINUTE.

MDY

The MDY function converts three-integer values into a DATE format. The first integer is the month and must evaluate to an integer in the range 1–12. The second integer is the day and must evaluate to a number in the range from 1 to however many days are in the particular month (28–31). The third expression is the year and must be a four-digit integer. Thus, the following MDY functions would each be valid:

MDY(7,1,1950)

returns a DATE of "07/01/50"

MDY(MONTH(TODAY), 1, YEAR(TODAY))

returns a DATE equal to the first day of the current month in the current year

Informix has extensive capabilities for manipulating dates and times, which can make for long and complex SQL statements. Using the three time-related data types and the time-related functions and keywords, you can accomplish almost any type of manipulation of time data. Unfortunately, getting there may be cryptic and painful. If you regularly do extensive date and time manipulation, you should understand all of the intricacies of these data structures.

Have fun!?