Oracle uses ROUND and EXTRACT for date operations

Oracle uses ROUND and EXTRACT for date operations:

1. SQL>SelectTo_char (sysdate,'Yyyy/mm/dd hh24: mi: ss')FromDual;
3. TO_CHAR (SYSDATE, 'yyyy/MM/DDHH2
4. ------------------------------
5. 18:13:20

ROUND (d, [fmt]): returns a rounding date value in the fmt Format.

D is the date, fmt is the format model, and cannot be the hour, minute, and second. The default value is DDD, that is, one day of the month.

• If fmt is "YEAR", it is rounded to January 1 of a YEAR, that is, the first half of the YEAR, and the last half of the YEAR is used as the next YEAR.

1. SQL>SelectTo_char (round (sysdate,'Year'),'Yyyy/mm/dd hh24: mi: ss')FromDual;
3. TO_CHAR (ROUND (SYSDATE,'Year'),
4. ------------------------------
5. 00:00:00

• If fmt is "MONTH", it is rounded to the day of a MONTH, that is, the first half of the MONTH, and the second half of the MONTH as the next MONTH.

1. SQL> select to_char (round (sysdate,'Month'),'Yyyy/mm/dd hh24: mi: ss') From dual;
3. TO_CHAR (ROUND (SYSDATE,'Month')
4. ------------------------------
5. 2012/04/01 00:00:00

• If fmt is "DDD", that is, a day in the month, the day closest to the day, the first half of the day will be taken away, and the next half day will be taken as the next day.

1. SQL>SelectTo_char (round (sysdate,'Ddd'),'Yyyy/mm/dd hh24: mi: ss')FromDual;
3. TO_CHAR (ROUND (SYSDATE,'Ddd'),'
4. ------------------------------
5. 00:00:00

• If fmt is "DAY", it is rounded to Sunday of the last week, that is, the first half of the week is taken away, and the second half is used as the next week.

1. SQL> select to_char (round (sysdate,'Day'),'Yyyy/mm/dd hh24: mi: ss') From dual;
3. TO_CHAR (ROUND (SYSDATE,'Day'),'
4. ------------------------------
5. 2012/04/01 00:00:00

EXTRACT (fmt FROM d): EXTRACT a specific part of a date.

Fmt is: YEAR, MONTH, DAY, HOUR, SECOND. YEAR, MONTH, and DAY can be of the DATA type or TIMESTAMP type. However, HOUR, MINUTE, and SECOND must be of the TIMESTAMP type.

1. SQL>SelectTo_char (sysdate,'Yyyy/mm/dd hh24: mi: ss')"Date",
2. 2 extract (YEAR FROMSYSDATE)"Year",
3. 3 extract (MONTH FROMSYSDATE)"Month",
4. 4 extract (DAY FROMSYSDATE)"Day",
5. 5 extract (HOUR FROMSYSTIMESTAMP)"Hour",
6. 6 extract (MINUTE FROMSYSTIMESTAMP)"Minute",
7. 7 extract (SECOND FROMSYSTIMESTAMP)"Second"
8. 8FromDual;
10. -- The result of HOUR matching is not added with the time zone, so the result of running in China is 8 hours less
11. DateYearMonthDayHourMinuteSecond
12. -------------------------------------------------------------------
13. 18:53:42 2012 4 3 10 53 42.79

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12