Compare report generation date with sysdate

User_R4VJD

Hi ,

Requirement: Report should be generated only one time per day.

Solution: I have to compare report generation date with sysdate, if its not equal then report will be processed only once per day.

Query:

select * from table1 where Trunc(report_generation_DATE) != TRunc(sysdate);

Whether this where condition will work fine in PLSQL.

i)Applying trunc on both side

ii) != --> condition

Frank Kulash

Hi, @User_R4VJD

Query:

select * from table1 where Trunc(report_generation_DATE) != TRunc(sysdate);

Whether this where condition will work fine in PLSQL.

Yes, that should work. I don't see any obvious mistakes.

It might be a little more efficient to use

WHERE report_generation_date >= TRUNC (SYSDATE)
AND   report_generation_date <  TRUNC (SYSDATE) + 1

but the difference might be so small you wouldn't notice it. It's valid to use TRUNC on both sides of an inequality (or equality) operation, but it's more efficient if you can avoid using TRUNC (or any function) on the column from the table, especially if the column is indexed. The != operator works with DATEs.

If report_generation_date is never in the future, you could just say:

WHERE report_generation_date < TRUNC (SYSDATE)

Either way is a good way to answer the question "When was the report run, except for today?" Is that really he question you need to ask, or are you more interested in "Was the report run today?"

I hope this answer your question. If not, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want to get from that data for a given value of SYSDATE, and an explanation of why you want those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

Frank Kulash

Hi, @User_R4VJD

Query:

select * from table1 where Trunc(report_generation_DATE) != TRunc(sysdate);

Whether this where condition will work fine in PLSQL.

Yes, that should work. I don't see any obvious mistakes.

It might be a little more efficient to use

WHERE report_generation_date >= TRUNC (SYSDATE)
AND   report_generation_date <  TRUNC (SYSDATE) + 1

but the difference might be so small you wouldn't notice it. It's valid to use TRUNC on both sides of an inequality (or equality) operation, but it's more efficient if you can avoid using TRUNC (or any function) on the column from the table, especially if the column is indexed. The != operator works with DATEs.

If report_generation_date is never in the future, you could just say:

WHERE report_generation_date < TRUNC (SYSDATE)

Either way is a good way to answer the question "When was the report run, except for today?" Is that really he question you need to ask, or are you more interested in "Was the report run today?"

I hope this answer your question. If not, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want to get from that data for a given value of SYSDATE, and an explanation of why you want those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

BluShadow

Yes you can use TRUNC in your SQL or PL/SQL code.

Yes you can use != or the <> conditions in SQL or PL/SQL.

Are you having an issue with it? If so, explain exactly what you're doing and what issue you are seeing.

User_R4VJD

Thanks