Forum Stats

  • 3,871,965 Users
  • 2,266,360 Discussions
  • 7,911,017 Comments

Discussions

Categories

comparing Last create date with sysdate

User_R4VJD
User_R4VJD Member Posts: 31 Green Ribbon
edited Jun 9, 2021 6:03AM in SQL & PL/SQL

Create date

Null


select * from table1

where create date < TRUNC(SYDATE)

Requirement : I have check create date should be less than sysdate.

if column is NULL, how to perform

Tagged:
· Share on TwitterShare on Facebook

Answers

  • S567
    S567 Member Posts: 456 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/4485427/comparing-last-create-date-with-sysdate

    What is your requirement what to do how create date is NULL ? Do you want to ignore NULL record while comparing ?

    I assume you requirement is something like below...

     with A as ( 

       select sysdate-3 createdate from dual union all 

       select sysdate-2 createdate from dual union all 

       select sysdate createdate from dual union all 

       select sysdate+1 createdate from dual union all 

       select null createdate from dual )

       select createdate from a 

       where nvl(createdate,sysdate) < trunc(sysdate)

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,713 Red Diamond
    edited Jun 9, 2021 11:10AM

    Hi, @S567

     where nvl(createdate,sysdate) < trunc(sysdate)

    does the same thing as what OP originally posted

    where create date < TRUNC(SYDATE)

    Neither condition includes rows where create_date is NULL. The former returns FALSE and the latter returns UNKNOWN; neither returns TRUE.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,908 Red Diamond
    edited Jun 9, 2021 12:11PM

    @S567 where nvl(createdate,sysdate) < trunc(sysdate)

    In addition to what Frank said, the above will prevent optimizer from using index on createdate if there is one (unless createdate is NOT NULL to begin with, then optimizer is smart enough to ditch nvl and will simply use createdate < trunc(sysdate) and use index).

    SY.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,713 Red Diamond

    Hi, @User_R4VJD

    select * from table1

    where create date < TRUNC(SYDATE)

    Requirement : I have check create date should be less than sysdate.

    if column is NULL, how to perform

    The code you posted does exactly what you describe: it checks if create_date is less than sysdate, and it performs (that is, it does not raise an error) if create_date is NULL. Perhaps it's not doing what you want, but that's a different question. For that, you have to explain what you want to do, and give some examples.

    Whenever you have any question, explain what you want, and give some examples. Post a little sample data, like this:

    CREATE TABLE  table1 (xid, create_date)  AS
SELECT 0,  NULL							      FROM dual UNION ALL
SELECT 10, TO_DATE ('08-Jun-2021 00:00:00', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALL
SELECT 11, TO_DATE ('08-Jun-2021 23:59:59', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALL
SELECT 20, TO_DATE ('09-Jun-2021 00:00:00', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALl
SELECT 21, TO_DATE ('09-Jun-2021 07:59:59', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALl
SELECT 22, TO_DATE ('09-Jun-2021 08:00:00', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALl
SELECT 23, TO_DATE ('09-Jun-2021 08:00:01', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALl
SELECT 24, TO_DATE ('09-Jun-2021 23:59:59', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALl
SELECT 30, TO_DATE ('10-Jun-2021 00:00:00', 'DD-Mon-YYYY HH24:MI:SS') FROM dual UNION ALL
SELECT 31, TO_DATE ('10-Jun-2021 23:59:59', 'DD-Mon-YYYY HH24:MI:SS') FROM dual
;

    Try not to use SYSDATE in the sample data. The people trying to help you might be in different time zones than you. Post something that anyone who runs it will get the exact same results, no matter where they are or what the NLS settings on their systems are.

    Then post the exact results you want from the given data. If the results depend on when the query is run, give a couple of examples, e.g. "If I run the query at 08:00:00 on June 9, 2021, then the results should be ... because .... But if I run the same query at ... then the results should be ... because ..."

    Always post the exact version of Oracle you're using (e.g. 12.2.0.1.0).

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Double post:

    https://community.oracle.com/tech/developers/discussion/4485428/comparing-dates#latest

    · Share on TwitterShare on Facebook
  • S567
    S567 Member Posts: 456 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16798348#Comment_16798348

    I assumed his requirement was to find records where create date should be less than sysdate.

    Requirement : I have check create date should be less than sysdate.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,713 Red Diamond

    Hi, @S567

    I assumed his requirement was to find records where create date should be less than sysdate.

    That's what I thought, too, but that's beside the point. Whatever OP wants to do, the suggestion you posted gets exactly the same results that he is already getting, and gets them less efficiently.

    · Share on TwitterShare on Facebook