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

0
votes

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

1
sqloracle
If you can't figure it out using the duplicate link, then drop a note here and someone can reopen the question for you. – Tim Biegeleisen
written the query above using the answer link posted above but getting error message. – user739115

1 Answers

0
votes

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')