CODEWITHSUNDEEP
sqloracle-database
user3370602
user3370602

Reputation:

Reason for using trunc function on dates in Oracle

I am currently working in a project on a Oracle database. I have observed in the application code that dates are almost never used directly. Instead, they are always used in conjunction with the trunc function (TRUNC(SYSDATE), TRUNC(event_date), etc.)

Can anyone explain the reason behind using the trunc function instead of using the date directly?

Upvotes: 2

Views: 4461

Answers (3)

Saule
Saule

Reputation: 589

If you the column in your table, for example event_date, is indexed, then avoid using trunc on the column because if you do that then Oracle can't use the index (otherwise, you can create a function based index)

so do not do:

select * from mytable where trunc(event_date) < date '2014-01-01'

but instead do

select * from mytable where event_date < date '2014-01-02'

In the second case, Oracle can do a range scan on the index on event_date, in the first case it has to do a full table scan.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30775

A DATE in Oracle has not only a date part, but also a time part. This can lead to surprising results when querying data, e.g. the query

with v_data(pk, dt) as (
  select 1, to_date('2014-06-25 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual union all
  select 2, to_date('2014-06-26 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual union all  
  select 3, to_date('2014-06-27 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual)
select * from v_data where dt = date '2014-06-25'

will return no rows, since you're comparing to 2014-06-25 at midnight.

The usual workaround for this is to use TRUNC() to get rid of the time part:

with v_data(pk, dt) as (
  select 1, to_date('2014-06-25 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual union all
  select 2, to_date('2014-06-26 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual union all  
  select 3, to_date('2014-06-27 09:00:00', 'YYYY-MM-DD hh24:mi:ss') from dual)
select * from v_data where trunc(dt) = date '2014-06-25'

Other, somewhat less frequently used approaches for this problem include:

  • convert both dates with to_char('YYYY-MM-DD') and check for equality
  • use a between clause: WHERE dt between date '2014-06-25' and date '2014-06-26'

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You use the trunc() function to remove the time component of the date. By default, the date data type in Oracle stores both dates and times.

The trunc() function also takes a format argument, so you can remove other components of the dates, not just the time. For instance, you can trunc to the nearest hour. However, without the format, the purpose is to remove the time component.

Upvotes: 2

Related Questions