Statement Where comparing Date(type date) variable with current date

Question

Hello. I am using Oracle SQL and need a query that returns values where the date is equal to the current date. Oracle has some word reserved for current date in the system?

My code:

insert into monitoracao(idseq, idsess, inst_name, elapsed_time, dateTime, fulltextsql, idsql)
SELECT idSeq.nextval,
sess.sid, 
inst.instance_name,
sqla.elapsed_time,
sqla.last_active_time,
sqla.sql_fulltext,
sqla.sql_id
FROM gv$sqlarea sqla, gv$session sess, gv$instance inst
WHERE sess.sql_hash_value = sqla.hash_value
AND sess.sql_address = sqla.address
AND sess.inst_id = inst.inst_id
AND elapsed_time > 10000000
-------------------------------------------------
select inst_name, dateTime 
from monitoracao, gv$sqlarea sqla
where monitoracao.DATETIME = SYSDATE;

Answer 1

Converts monitoring.datetime and sysdate to date and in the same notation.

SELECT inst_name, dateTime 
   FROM monitoracao, gv$sqlarea sqla
   WHERE TO_DATE(monitoracao.DATETIME, 'DD-MM-YYYY') = TO_DATE(SYSDATE, 'DD-
   MM-YYYY');

Answer 2

Okay, guys, problem solved here. I was not aware that SYSDATE (and other variables of type DATE) took, beyond the day, month and year, the Hours, minutes and seconds... So, to be able to compare only the dates, I used the command

alter session set nls_date_format = 'dd-mm-yyyy'

Answer 3

I believe your problem is that you’re validating the full date. As you just want today’s date, make the comparison by formatting the dates to ignore hours:

select inst_name, dateTime 
from monitoracao, gv$sqlarea sqla
where (monitoracao.DATETIME, 'MM-DD-YYYY') = (SYSDATE, 'MM-DD-YYYY');