Forum Stats

  • 3,768,297 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

comparing date's

User_R4VJD
User_R4VJD Member Posts: 31 Green Ribbon

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

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

    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
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    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
    EdStevens Member Posts: 28,519 Gold Crown

    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.