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

0

Hello. I am using Oracle SQL and need a query that returns values where the date is equal to the current date. Does Oracle have any reserved word 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;
    
asked by anonymous 29.09.2017 / 16:38

3 answers

1

Converts the monitoracao.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');
    
04.10.2017 / 15:44
0

I think your problem is that you are validating the complete date. As you just want today's date, make the comparison by formatting the dates to skip hours:

select inst_name, dateTime 
from monitoracao, gv$sqlarea sqla
where (monitoracao.DATETIME, 'MM-DD-YYYY') = (SYSDATE, 'MM-DD-YYYY');
    
29.09.2017 / 16:44
0

Well guys, problem solved here. I did not have the knowledge that SYSDATE (and other variables of type DATE) took, in addition to the day, month and year, the Hours, minutes and seconds ... So, to be able to compare only the dates, p>

alter session set nls_date_format = 'dd-mm-yyyy'
    
23.10.2017 / 15:17