Reputation: 105
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
:
Upvotes: 0
Views: 839
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
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