comparing date's

User_R4VJD

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

User_H3J7U

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

Billy Verreynne

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.

Frank Kulash

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

EdStevens

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.

EdStevens

Double post:

https://community.oracle.com/tech/developers/discussion/4485427/comparing-last-create-date-with-sysdate#latest