Date and Time Calculation functions of ORACLE Functions

1 ADD_MONTHS

Format:ADD_MONTHS (D, N)

Note:Returns the date and time corresponding to date and time D plus N months. If N is the timing value, it indicates that D is followed; if N is negative, it indicates that D is before; if N is the decimal number, it automatically deletes the decimal part first, and uses the integer part.

Example:

SQL> SELECT ADD_MONTHS (SYSDATE, 7) A, ADD_MONTHS (SYSDATE,-7) B, ADD_MONTHS (SYSDATE, 7.9) C FROM DUAL;

A B C

-------------------------------------------------------

2015/1/16:07:02 16:07:02

2 CURRENT_DATE

Format: CURRENT_DATE

Note:Returns the date corresponding to the current session time zone.

Example:SQL> SELECTCURRENT_DATE A FROM DUAL;

A

-------------------

2014/6/

3 CURRENT_TIMESTAMP

Format:CURRENT_TIMESTAMP ([P])

Note:Returns the timestamp of the date and time corresponding to the current session time zone. P is the precision, can be an integer between the O-9, the default is 6

Example:

SQL> SELECT CURRENT_TIMESTAMP A, CURRENT_TIMESTAMP (9) B FROM DUAL;

A B

Bytes ----------------------------------------------------------------------------------------------

--14 04.12.33.402000 PM +-1404.12.33.402000000 PM +

4 DBTIMEZONE

Format:DBTIMEZONE

Note:Returns the database time zone.

Example:

SQL> SELECT DBTIMEZONE A FROM DUAL;

A

------

+ 00: 00

5 EXTRACT

Format:EXTRACT (C, from d)

Note:Returns part C specified in Date and Time D. The value of C is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR. The specified C must exist in D.

Example:

Extraction year, month, and day

SQL> select extract (year from sysdate) A, EXTRACT (month from sysdate) B, EXTRACT (DAYFROM SYSDATE) C FROM DUAL;

A B C

------------------------------

2014 6 20

Extraction time, minute, and second cannot be extracted from SYSDATE. The UTC Standard Time returned by SYSTIMESTAMP

SQL> select extract (hour from your imestamp) A, EXTRACT (minute from your imestamp) B, EXTRACT (SECOND FROM your imestamp) C FROM DUAL;

A B C

------------------------------

8 26 50.052

6 LAST_DAY

Format:LAST_DAY (D)

Note:Returns the last day of the month where date D is located.

Example:

SQL> SELECT LAST_DAY (SYSDATE) A FROM DUAL;

A

------------------

2014/6/3016: 40: 08

7 LOCALTIMESTAMP

Format:LOCALTIMESTAMP ([p])

Note:Returns the date and time of the current session time zone. P is an integer between 0 and 9. The default value is 6. Different from CURRENT_TIMESTAMP in the returned data type

Example:

SQL> SELECT LOCALTIMESTAMP (4) A FROM DUAL;

A

--------------------------------------------------------------------------------

-14 04.42.09.2800 pm

8 MONTHS_BETWEEN

Format:MONTHS_BETWEEN (D1, D2)

Note:Returns the number of months between the D1 and D2 dates. If D1 is less than D2, a negative number is returned. If D1 and D2 have the same number of days or are all at the end of the month, an integer is returned. Or Oracle uses 31 days as the decimal part of the quasi-calculation result.

Example:

SQL> SELECT MONTHS_BETWEEN (SYSDATE, TO_DATE ('2017-12-31 ', 'yyyy-MM-dd') A, MONTHS_BETWEEN (SYSDATE, TO_DATE ('2017-12-1 ', 'yyyy-MM-dd') a from dual;

A

--------------------

5.66771318-17.364544

9 NEXT_DAY

Format:NEXT_DAY (D, C)

Note:Returns the next C after the date D. C is a string that represents the full name of a day in a week (such as Monday and Tuesday) in the current session language. It can also be a value.

Example:SQL> SELECTNEXT_DAY (SYSDATE, 3) A, NEXT_DAY (SYSDATE, 'monday') B FROM DUAL;

A B

-------------------------------------------

2014/6/2416: 48: 56 16:48:56

10 ROUND

Format:ROUND (date, fmt)

Note:The date is rounded in.

Fmt is year or YY, rounded to the last several years or months by ** 1-6 months and 7-12 months **

Fmt is month or MM, rounded to the last day of the month by ** 1-15 and 16-30 **

Fmt is day. It is rounded to the nearest Sunday by ** Monday to Wednesday and Thursday to Sunday **.

Fmt is DD. If the hour exceeds 12, move forward to 1

Fmt is HH. If the score exceeds 30, move forward to 1.

Fmt is minute MI, to minute, and the number of seconds 30 is the dividing line, which is equivalent to rounding 5. If the second exceeds 30, move forward 1

Fmt is SS in seconds, accurate to seconds

Example:

SQL> SELECT ROUND (TO_DATE ('2017-11-24 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'yy') AFROM DUAL;

A

-----------

SQL> SELECT ROUND (TO_DATE ('2017-11-24 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'mm') AFROM DUAL;

A

-----------

2013/12/1

SQL> SELECT ROUND (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'day'), ROUND (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'dd') B FROMDUAL;

A B

----------------------

2013/11/24 2013/11/25

SQL> SELECT ROUND (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'hh'), ROUND (TO_DATE ('1970-11-2013: 31: 11', 'yyyy-MM-DD HH24: MI: ss'), 'mi') B FROM DUAL;

A B

---------------------------------------

2013/11/2511: 00: 00 10:31:00

11 SESSIONTIMEZONE

Format:SESSIONTIMEZONE

Note:Returns the time zone of the current session.

Example:

SQL> SELECT SESSIONTIMEZONE A FROM DUAL;

A

---------------------------------------------------------------------------

+ 08:00

12 SYS_EXTRACT_UTC

Format:SYS_EXTRACT_UTC (datetime_wuth_timezone)

Note:Returns UTC time.

Example:

SQL> SELECT SYS_EXTRACT_UTC (SYSTIMESTAMP) A FROM DUAL;

A

--------------------------------------------------------------------------------

24-6-14 03.05.09.136892 AM

13 SYSDATE

Format:SYSDATE

Note:Returns the current time, the operating system time when the database resides.

Example:

SQL> SELECT SYSDATE A FROM DUAL;

A

------------------

2014/6/2411: 06: 24

14. SYSTIMESTAMP

Format:SYSTIMESTAMP

Note:Returns the system time, including time zone information, in microseconds.

Example:

SQL> SELECT distinct imestamp A FROM DUAL;

A

--------------------------------------------------------------------------------

24-6-14 11.09.50.997760 AM +

15 TRUNC

Format:TRUNC (d [, fmt])

Note:Returns the date and time when there is only one parameter d.

If fmt is yyyy or yy, the first day of the current year is returned.

If fmt is mm, the first day of the month is returned.

If fmt is dd, the current year, month, and day are returned.

Returns the first day of the current week when fmt is d.

When fmt is hh, It is intercepted to the current hour.

When fmt is mi, It is intercepted to the current minute.

Example:

SQL> SELECT TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss') A FROM DUAL;

A

-----------

SQL> SELECT TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'yyyy'), TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'yy') B FROM DUAL;

A B

----------------------

2013/1/1 2013/1/1

SQL> SELECT TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'mm'), TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'dd') B FROM DUAL;

A B

----------------------

2013/11/1 2013/11/25

SQL> SELECT TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'D') A FROMDUAL;

A

-----------

SQL> SELECT TRUNC (TO_DATE ('2017-11-25 10:31:11 ', 'yyyy-MM-DD HH24: MI: ss'), 'hh'), TRUNC (TO_DATE ('1970-11-2013: 31: 11', 'yyyy-MM-DD HH24: MI: ss'), 'mi') B FROM DUAL;

A B

--------------------------------------

2013/11/2510: 00: 00 10:31:00 renew