When using to_char you are comparing strings.
Copyto_char(date '2000-01-20', 'DD-MM-YYYY') > to_char(date '2018-05-14', 'DD-MM-YYYY')
because '20-01-2000' is greater than '14-05-2018', because of the first letters in the strings: '2' > '1'.
And CURRENT_DATE is hardly ever used, because it uses your computer's time, rather than the database time, so you can easily be some hours off. Use SYSDATE instead.
Videos
When using to_char you are comparing strings.
Copyto_char(date '2000-01-20', 'DD-MM-YYYY') > to_char(date '2018-05-14', 'DD-MM-YYYY')
because '20-01-2000' is greater than '14-05-2018', because of the first letters in the strings: '2' > '1'.
And CURRENT_DATE is hardly ever used, because it uses your computer's time, rather than the database time, so you can easily be some hours off. Use SYSDATE instead.
I would suggest using this query
Copyselect * from Inventory where trunc(Placement_End_Dt) = trunc(sysdate);
Oracle Date columns also store a timestamp by default, so unless the records were the same down to the second, they won't match. When you use trunc() on a date column, it truncates the timestamp and leaves just the date.
To select current date (Today) before midnight (one second before) you can use any of the following statements:
SELECT TRUNC(SYSDATE + 1) - 1/(24*60*60) FROM DUAL
SELECT TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND FROM DUAL;
What it does:
- Sum one day to
SYSDATE:SYSDATE + 1, now the date is Tomorrow - Remove time part of the date with
TRUNC, now the date is Tomorrow at 00:00 - Subtract one second from the date:
- 1/(24*60*60)or- INTERVAL '1' SECOND FROM DUAL, now the date is Today at 11:59:59
Note 1: If you want to check date intervals you might want to check @Allan answer below.
Note 2: As an alternative you can use this other one (which is easier to read):
SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
- Remove time part of the current date with
TRUNC, now the date is Today at 00:00 - Add a time interval of
23:59:59, now the date is Today at 11:59:59
Note 3: To check the results you might want to add format:
SELECT TO_CHAR(TRUNC(SYSDATE + 1) - 1/(24*60*60),'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND,'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE) + INTERVAL '23:59:59','yyyy/mm/dd hh24:mi:ss') FROM DUAL
Personally, I dislike using one second before midnight. Among other things, if you're using a timestamp, there's a possibility that the value you're comparing to falls between the gaps (i.e. 23:59:59.1). Since this kind of logic is typically used as a boundary for a range condition, I'd suggest using "less than midnight", rather than "less than or equal to one second before midnight" if at all possible. The syntax for this simplifies as well. For instance, to get a time range that represents "today", you could use either of the following:
date_value >= trunc(sysdate) and date_value < trunc(sysdate) + 1
date_value >= trunc(sysdate) and date_value < trunc(sysdate) + interval '1' day
It's a little more cumbersome than using between, but it ensures that you never have a value that falls outside of the range you're considering.
You may not be seeing any data because SYSDATE and the update_date column have a time component (all DATE and TIMESTAMP data types have this) but the IDE you are using is just choosing not to show it to you. To change this behaviour in SQL Developer see here.
This will use any indexes you have on the update_date column:
SELECT *
FROM ack
WHERE update_date >= TRUNC( SYSDATE ) - INTERVAL '1' DAY
AND update_date < TRUNC( SYSDATE );
This will use a function-based index on TRUNC( update_date) but will not use an index on the update_date column:
SELECT *
FROM ack
WHERE TRUNC( update_date ) = TRUNC( SYSDATE ) - INTERVAL '1' DAY;
Try:
select * from ack where trunc(update_date) = trunc(SYSDATE - 1)
What you posted is how dates are presented they probably contain also part with hours and minutes and so on. trunc cuts precision to days. Please execute:
select to_char(update_date,'dd-mon-yyyy hh24:mi:ss') update_date, sysdate - 1 as curr_date from ack;
You're comparing those values and they are not identical. That's why you get no results.
It only seems to because that is what it is printing out. But actually, you shouldn't write the logic this way. This is equivalent:
insert into errortable (dateupdated, table1id)
values (sysdate, 1083);
It seems silly to convert the system date to a string just to convert it back to a date.
If you want to see the full date, then you can do:
select TO_CHAR(dateupdated, 'YYYY-MM-DD HH24:MI:SS'), table1id
from errortable;
You may try with below query :
INSERT INTO errortable (dateupdated,table1id)
VALUES (to_date(to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ),1083 );
To view the result of it:
SELECT to_char(hire_dateupdated, 'dd/mm/yyyy hh24:mi:ss')
FROM errortable
WHERE table1id = 1083;