comparing to datetime columns in an Oraclew query matching just on Date

Experts,

I have two datetime fields in Oracle that I just want to compare the Date part  because the time part is different.  Does anyone know how to code this in Oracle?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Use trunc:
SELECT trunc(t5.maintdate) ...

SELECT trunc(T4.DATE_TIME)...
0
 
slightwv (䄆 Netminder) Commented:
A few ways but it depends on exactly what you are wanting.

If there is no index on the fields:
trunc(column_name) will zero out the time portion of dates.

It is normally better to use >= and < because there are typically indexes on date fields:
column_name >= some_date and column_name < some_other_date+1

If you can provide more information about what you mean by 'compare' we can adjust our suggestions.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
This is my query,   I am not getting equal as is because Table 4 has a time and Table 5 time is zeroes

Case
when (SELECT t5.maintdate
          FROM Table5 T5
          WHERE T5.CLAIM = T1.CLAIM
          AND T5.OPID = 'AAAA')  =
       (SELECT T4.DATE_TIME
       FROM Table4 T4
        WHERE T4.CLAIM = T1.CLAIM
        AND T4.TERMID = 'AAAA')
                    then  'YES'  
        else '   '
end AUTO_ADJ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.