CODEWITHSUNDEEP
sqloracle-database
user739115
user739115

Reputation: 1187

how to write to query to fetch for particular date and also range of dates

audit_modifier is VARCHAR2(30 CHAR) and audit_modifier values are stored in this format[2018-01-18T17:19:47.285Z].

then how to write to query to fetch for particular date and also range of dates.

SELECT * FROM TABLE where audit_modifier = '2018-01-18';

getting below error message.

select * from TABLE WHERE trunc(audit_modifier) BETWEEN TO_DATE('2018-01-16', 'YYYY-MM-DD') AND TO_DATE('2018-01-16', 'YYYY-MM-DD');

ORA-00932: inconsistent datatypes: expected NUMBER got DATE 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 10 Column: 29

Upvotes: 0

Views: 334

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

This answer assumes that you are storing timestamp information as text in the following format:

2018-01-18T03:22:48.317Z

You should be storing your date information in a date column, but we can workaround this. We can convert this string to a timestamp using TO_TIMESTAMP, then truncate it to a date:

SELECT *
FROM TABLE
WHERE TRUNC(TO_TIMESTAMP('2018-01-18T03:22:48.317Z',
                         'YYYY-MM-DDTHH24:MI:SS.FFFZ')) =
    TO_DATE('2018-01-18', 'YYYY-MM-DD')

Upvotes: 0

Related Questions