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

Asked

Viewed 1,241 times

0

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;
  • what’s wrong with your consultation? where monitoracao.DATETIME = SYSDATE doesn’t work??

  • I noticed that in select, is being returned a date with format (day-month-year), SYSDATE returns the same format? or the format (month-day-year)?

  • Returns nothing, as if the data were not equal.

3 answers

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');

0

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'

0

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');
  • Yeah, it went wrong here ORA-00920: invalid relational operator I’ll keep trying

Browser other questions tagged

You are not signed in. Login or sign up in order to post.