0

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

user739115
  • 1,117
  • 5
  • 20
  • 41

1 Answers1

0

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')
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • audit_modifier is varchar2 column.why no records are coming when we execute for particular date?.SELECT * FROM TABLE WHERE audit_modifier = '2018-01-18';;values exists like this in the table 2018-01-18T03:22:48.317Z 2018-01-18T06:22:34.949Z – user739115 Jan 18 '18 at 08:09
  • `SELECT * FROM TABLE WHERE SUBSTR(audit_modifier, 1, 10) = '2018-01-18'` ... but _don't_ store your dates as text. – Tim Biegeleisen Jan 18 '18 at 08:14
  • this is old table so we cannot change it. – user739115 Jan 18 '18 at 08:18