Oracle sql query not showing specific date

Question

This is my first time with oracle database. So I save data with date 30/04/20 and I want to retrieve it. So I use SELECT * FROM USER_ACTION WHERE ACTION_DATE_TIME <= '30-APR-20' order by ACTION_DATE_TIME desc but no data with date 30/04/20 are shown. However when I use SELECT * FROM USER_ACTION WHERE ACTION_DATE_TIME <= '01-MAY-20' order by ACTION_DATE_TIME desc, I can see the data. Is there anyway that I can get date with exact date? no need to put extra +1 day to get it.

This is result when use 30-APR-20:

This is result when use 01-MAY-20:

A few observations. 1) you insist you don't want to change the data, but no one is suggesting changing the data. The suggestion is to change your query to match the reality of the data. 2) " WHERE ACTION_DATE_TIME <= '30-APR-20'" . Assuming ACTION_DAT_TIME is, as it should be, a DATE or a TIMESTAMP, you are comparing that against a string, forcing orcle to an implied TO_DATE. This will bite you. Better to explicity include the proper to_date() yourself. 3) 2-digit years. You are repeating the mistakes that caused Y2k. Always, always use 4-digit years. — EdStevens
– EdStevens, Commented Apr 30, 2020 at 12:54

Tim Biegeleisen · Accepted Answer · 2020-04-30 03:59:00Z

1

Given that your ACTION_DATE_TIME column be a datetime, with time component, if you want to include 30th April 2020 proper, you should be using this inequality:

SELECT *
FROM USER_ACTION
WHERE ACTION_DATE_TIME < date '2020-05-01'
ORDER BY ACTION_DATE_TIME DESC;

This will include all dates strictly less than 1st May 2020, which include all of 30th April 2020.

If the date value is coming from the outside, then just add one day to it:

SELECT *
FROM USER_ACTION
WHERE ACTION_DATE_TIME < date '2020-05-01' + 1
ORDER BY ACTION_DATE_TIME DESC;

edited Apr 30, 2020 at 3:59

answered Apr 30, 2020 at 3:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

anggor Over a year ago

the date actually is user input data, so it is not ideal to change it. so I just try to use what I get directly. your answer is suggesting me to add an extra day for everytime time user submit a date

Tim Biegeleisen Over a year ago

The answer I have given you is best practice when searching for datetime ranges limited to certain ending day.

anggor Over a year ago

Ok noted that. Thanks for info. But still, I prefer not to change the user input data

Tim Biegeleisen Over a year ago

Adding one day to a date is simple, and just requires doing + 1.

anggor Over a year ago

Ok. Thank you a lot. Did not know can just add +1 directly to query.

zealous · Accepted Answer · 2020-04-30 03:51:44Z

1

use trunc to convert date time to date as below

SELECT * 
FROM USER_ACTION 
WHERE TRUNC(ACTION_DATE_TIME) <= '30-APR-20'  
order by ACTION_DATE_TIME desc

answered Apr 30, 2020 at 3:51

zealous

7,5434 gold badges20 silver badges39 bronze badges

5 Comments

anggor Over a year ago

Can you explain what TRUNC does?

Tim Biegeleisen Over a year ago

Note that this option leaves the WHERE clause not sargable, meaning any index on ACTION_DATE_TIME cannot be used.

zealous Over a year ago

@TimBiegeleisen I had no idea about it, Thanks.

Tim Biegeleisen Over a year ago

@zealous Your answer is completely valid, and is another way to handle the OP's use case. But, in terms of performance, it's always best to leave the possibility of using an index.

micklesh Over a year ago

@TimBiegeleisen unless there's an index on trunc(action_date_time) or the table is partitioned. still, your point is abosolutely valid

Collectives™ on Stack Overflow

Oracle sql query not showing specific date

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related