Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
comparing date's
Create_date
09-06-21
08-06-21
07-06-21
select * from table1
where create_date < TRUNC(sysdate);
Its returning, 08-06-21,07-06-21.
its should return , no rows
Reuirement : if create date = sysdate
we should not create order again for the sydate.
Order should be created for only one time per day
Answers
-
Its returning, 08-06-21,07-06-21.
with t(d) as ( select date '2021-06-06'+level from dual connect by level<=3) select to_char(d, 'dd-mm-yyyy') d from t where d < trunc(sysdate); D ---------- 07-06-2021 08-06-2021
Order should be created for only one time per day
Unique constraint
-
Date datatype includes a time portion. Take that into consideration.
Date values are implicitly converted to strings when displayed. Do not confuse strings as dates. Explicit conversions to and from dates are the safe and recommended approach.
If only one order per day is allowed, enforce this via a unique constraint.
-
Hi, @User_R4VJD
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.
If the results depend on when the query is done, then post a couple of different run times and the exact results you want from each, given the same sample data.
If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.
Always say which version of Oracle you're using (e.g. 12.2.0.1.0).
-
What is the data type of CREATE_DATE? Are you inadvetently trying to compare strings and not dates?
And your example is showing a two-digit year. C'mon, man. We remediated Y2k over 20 years ago, and now you are trying to recreate it.
-
