Using TRUNC and ROUND on dates

Maybe this is old news to some, but I recently became aware that it is possible to use TRUNC and ROUND not just on a NUMBER, but also on a DATE value.

For example, you can get the start of the month for a given date (using TRUNC), or the "closest" start of the month, rounded forward or backwards in time appropriate (using ROUND):

?

1 2 3 4 5 6

select sysdate,   trunc(sysdate, 'YYYY') as trunc_year,   trunc(sysdate, 'MM') as trunc_month,   round(sysdate, 'MM') as round_month,   round(sysdate + 15, 'MM') as round_month2 from dual

The above gives the following results:

?

1 2 3

SYSDATE                   TRUNC_YEAR                TRUNC_MONTH               ROUND_MONTH               ROUND_MONTH2              ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- 06.04.2010 20:10:56       01.01.2010 00:00:00       01.04.2010 00:00:00       01.04.2010 00:00:00       01.05.2010 00:00:00

Somewhat related to this topic is the relatively obscure (?) EXTRACT function, which allows you to extract a part of a DATE:

?

1 2 3 4 5

select sysdate,   extract(day from sysdate) as extract_day,   extract(month from sysdate) as extract_month,   extract(year from sysdate) as extract_year from dual

Which gives the following results:

?

1 2 3

SYSDATE                   EXTRACT_DAY            EXTRACT_MONTH          EXTRACT_YEAR           ------------------------- ---------------------- ---------------------- ---------------------- 06.04.2010 20:13:01       6                      4                      2010

If you try to extract the "hour", "minute" or "second" from a DATE, however, you get an ORA-30076: invalid extract field for extract source.

For some reason, these only work on TIMESTAMP values, not on the DATE datatype (which seems like an arbitrary limitation to me). Nevertheless:

?

1 2 3 4 5

select systimestamp,   extract(hour from systimestamp) as extract_hour,   extract(minute from systimestamp) as extract_minute,   extract(second from systimestamp) as extract_second from dual

The above gives the following results:

?

1 2 3

SYSTIMESTAMP  EXTRACT_HOUR           EXTRACT_MINUTE         EXTRACT_SECOND         ------------- ---------------------- ---------------------- ---------------------- 06.04.2010 20.17.12,047000000 +02:00 18                     17                     12,047