AntDB-Oracle Compatibility Developer’s Manual P3–39

ADD_MONTH

The ADD_MONTHS function adds (or subtracts if the second argument is negative) the specified number of months to the specified date.

The date in the execution result and the date of the month in the given date are the same, unless the specified date is the last day of the month, in which case the resulting date is the last day of the month in the execution result.

Example:

SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;

ADD_MONTHS('13-JUN-07',4)  
----------------------------
 2007-10-13 00:00:00
(1 row)

SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;

ADD_MONTHS('31-DEC-06',2)  
----------------------------
 2007-02-28 00:00:00
(1 row)


SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;

ADD_MONTHS('31-MAY-04',-3)  
-----------------------------
 2004-02-29 00:00:00
(1 row)

EXTRACT

The EXTRACT function is used to get subfields like year or hour from a date/time field. The return value of the EXTRACT function is of type number. The following are valid field names.

• YEAR

A field indicating the year.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                2001
(1 row)

• MONTH

Indicates the month of the year (1-12).

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40')  
----------------------------------------------------------
                                                    2
(1 row)

• DAY

Indicates the date in a month (1-31).

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                 16
(1 row)

• HOUR

Indicates the hour field (0-23).

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40')  
-----------------------------------------------------
                                                  20
(1 row)

• MINUTE

Indicates the minute field (0-59).

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40')  
---------------------------------------------------------
                                                    38
(1 row)

• SECOND

Indicates the seconds field, including the fractional part (0-59).

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45') FROM DUAL;

 EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45')  
-----------------------------------------------------------
                                                    40.45
(1 row)