Tuesday, April 6, 2010

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):

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:

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:

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:

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:

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:

------------- ---------------------- ---------------------- ---------------------- 
06.04.2010 20.17.12,047000000 +02:00 18                     17                     12,047                 


Anonymous said...

To extract hour, minute, second from date :
select sysdate,
extract(hour from cast(sysdate as timestamp)) as extract_hour,
extract(minute from cast(sysdate as timestamp)) as extract_minute,
extract(second from cast(sysdate as timestamp)) as extract_second
from dual;

Buzz Killington said...

I have to admin I'm surprised this is new to you - I've seen some excellent code from you in the past.

But as they say if you can learn something new every day... :)

David Njoku said...

Thanks for this. I'd never heard of the extract function before. (And now that I know I'll probably forget it instantly! - but that's not your fault)