comparing Last create date with sysdate

User_R4VJD

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

S567

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)

Frank Kulash

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.

Solomon Yakobson

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

Frank Kulash

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

EdStevens

Double post:

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

S567

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.

Frank Kulash

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.