Forum Stats

  • 3,782,436 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

Categories

Compare report generation date with sysdate

User_R4VJD
User_R4VJD Member Posts: 31 Green Ribbon
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 Red Diamond
    edited Jun 7, 2021 3:09PM Accepted Answer

    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).

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 Red Diamond
    edited Jun 7, 2021 3:09PM Accepted Answer

    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).

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • User_R4VJD
    User_R4VJD Member Posts: 31 Green Ribbon

    Thanks

    · Share on TwitterShare on Facebook