CODEWITHSUNDEEP
sqloracle-database
Baalback
Baalback

Reputation: 461

Difference between function trunc() Vs AND in datetime column

Is this query 

select 1 from tab1 where
TRUNC(tab1.T_DATE) = TRUNC(to_date('2018-08-31','yyyy-mm-dd'))

Similar to this ?

select 1 from tab1 where    
        tab1.T_DATE >= to_date('2018-08-31','yyyy-mm-dd')
        And tab1.T_DATE < to_date('2018-09-01','yyyy-mm-dd')

Then when I need to use function Trunc() ? I am asking the difference because I have a query using Trunc() and the query needs index(sure I don't want to use FBI), so I am replacing the function with AND. However I am worried that there might be some difference.

Data Example:

31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018 10:19:02 PM
31/08/2018 07:55:01 PM
31/08/2018 1:08:54 PM
31/08/2018 1:18:44 PM
31/08/2018 1:45:34 PM
31/08/2018 1:53:57 PM
31/08/2018 1:59:01 PM
31/08/2018 2:04:19 PM
31/08/2018 4:06:56 PM

Upvotes: 1

Views: 1591

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Yes, the two queries do exactly the same thing.

Oracle lacks a real DATE datatype. Its DATE is actually a DATETIME, which has confused people again and again :-) You can get to a real data column by using a check constraint that rejects dates with a time part or with a trigger that removes that part.

As to your table that really does have datetimes and the two queries selecting a day's rows:

The first query is more readable. People often hold against that, that the second query can benefit from indexes on the T_DATE column. However, as Oracle features function indexes, you would then use an index on TRUNC(t_date) and the argument is void.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You are asking if these are equivalent:

where TRUNC(tab1.T_DATE) = TRUNC(to_date('2018-08-31','yyyy-mm-dd'))

where tab1.T_DATE >= to_date('2018-08-31','yyyy-mm-dd') and
      tab1.T_DATE < to_date('2018-09-01','yyyy-mm-dd')

Yes. They both bring back all values of T_DATE on 2018-08-31, regardless of the date. TRUNC() when applied to a date removes the time component. Hence, it is not needed on the right side of the first expression.

I would write this as:

where tab1.T_DATE >= date '2018-08-31' and
      tab1.T_DATE < date '2018-09-01'

because this can use an index on tab1(T_DATE).

Upvotes: 3

Related Questions