CODEWITHSUNDEEP
sqloracle-database
anggor
anggor

Reputation: 105

Oracle sql query not showing specific date

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:

Query result

This is result when use 01-MAY-20:

Query result 2

Upvotes: 0

Views: 839

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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;

Upvotes: 1

zealous
zealous

Reputation: 7503

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

Upvotes: 1

Related Questions