CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

Inconsistent Datatypes: Expected DATE got NUMBER

I am trying to add a Where clause that only grabs data from that particular day (trunc(hr, 'dd')) but when I put in a sample value for HR (type: DATE WITH TIME ZONE) I receive the following error:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Below is some sample data for the HR Column:

**HR**
25-DEC-16 12.00.00.000000000 AM AMERICA/NEW_YORK
30-DEC-16 06.00.00.000000000 AM US/EASTERN

Below is my (sample) query:

select *
  from value v
  where trunc(v.hr, 'dd') = 
        trunc('01-DEC-16 12.00.00.000000000 AM AMERICA/NEW_YORK', 'dd')
    and v.code = 'DEFAULT';

Do I need to convert something? I've tried using the TO_DATE function but I don't think there is a date format that fits my sample data...

Thanks in advance!

Upvotes: 0

Views: 7487

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

There are several issues in your WHERE condition.

TRUNC(..., 'DD') returns a DATE value, i.e. you lose all time zone information. I would use this one

WHERE TRUNC(SYS_EXTRACT_UTC(hr)) = TRUNC(SYS_EXTRACT_UTC(TIMESTAMP '2016-12-01 12:00:00 AMERICA/NEW_YORK'))

NB, you can skip DD as it is the default.

Upvotes: 2

Related Questions