Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.7K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 387 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
comparing Last create date with sysdate

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