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

Question

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

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
– Tim Biegeleisen, Commented Jan 18, 2018 at 7:04
written the query above using the answer link posted above but getting error message. — user739115
– user739115, Commented Jan 18, 2018 at 7:23

Tim Biegeleisen · Accepted Answer · 2018-01-18 08:27:37Z

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

edited Jan 18, 2018 at 8:27

answered Jan 18, 2018 at 7:28

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user739115 Over a year ago

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

Tim Biegeleisen Over a year ago

SELECT * FROM TABLE WHERE SUBSTR(audit_modifier, 1, 10) = '2018-01-18' ... but don't store your dates as text.

user739115 Over a year ago

this is old table so we cannot change it.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related