How to compare two columns with only date not time

Question

Table name is : AIRPORT

Arriving	Departure
01-04-22 12:00:00	01-04-22 12:00:00
01-04-22 12:00:00	01-04-22 12:00:00
01-04-22 12:00:00	01-04-22 16:00:00

If I'm trying to compare using:

select * from airport where arriving= departure

Results:

Arriving	Departure
01-04-22 12:00:00	01-04-22 12:00:00
01-04-22 12:00:00	01-04-22 12:00:00

So I want all the three rows as my results, i want to compare just date not the time.

Can any one in simple query explain this.

Does this answer your question? [MySQL compare DATE string with string from DATETIME field](https://stackoverflow.com/questions/2758486/mysql-compare-date-string-with-string-from-datetime-field) — RiggsFolly, Apr 13 '23 at 16:00
Ah thats what happens when you spam tags :) I see you are not using MySQL — RiggsFolly, Apr 13 '23 at 16:03
Does this answer your question? [How to compare two DATE values based only on date part in Oracle?](https://stackoverflow.com/questions/7433363/how-to-compare-two-date-values-based-only-on-date-part-in-oracle) — astentx, Apr 14 '23 at 07:25

score 1 · Answer 1 · answered Apr 13 '23 at 16:09

The DATE data type in Oralce is a misnomer. It is a datetime really, consisting of the date part and a time part. In order to compare the date part only, truncate the datetime to midnight with TRUNC:

select *
from airport
where trunc(arriving) = trunc(departure);

score 0 · Answer 2 · answered Apr 15 '23 at 09:37

Oracle DATE is formed of 7 bytes (centuries, years, months, days, hours, minutes, seconds) to compare just the date part of it you should get ridd of the time part. It can be done in different ways. You can use any of them that suites you the best. Below are sample data, SQL Select command with three different Where Clauses all giving the same result.

WITH        --  Sample data
    tbl (ID, ARRIVING, DEPARTURE) AS
        (
            Select 1, To_Date('01-04-22 12:00:00', 'dd.mm.yy hh24:mi:ss'), To_Date('01-04-22 12:00:00', 'dd.mm.yy hh24:mi:ss') From Dual Union All
            Select 2, To_Date('01-04-22 12:00:00', 'dd.mm.yy hh24:mi:ss'), To_Date('01-04-22 12:00:00', 'dd.mm.yy hh24:mi:ss') From Dual Union All
            Select 3, To_Date('01-04-22 12:00:00', 'dd.mm.yy hh24:mi:ss'), To_Date('01-04-22 16:00:00', 'dd.mm.yy hh24:mi:ss') From Dual 
        )
--
--  SQL 
Select      ID, ARRIVING, DEPARTURE
From        tbl
Where       TRUNC(ARRIVING, 'dd') = TRUNC(DEPARTURE, 'dd')    -- truncate to the day
--  or
Where       To_Char(ARRIVING, 'dd.mm.yyyy') = To_Char(DEPARTURE, 'dd.mm.yyyy')    -- convert date part to char
--  or
Where       To_Number(To_Char(ARRIVING, 'yyyymmdd')) = To_Number(To_Char(DEPARTURE, 'yyyymmdd'))    --  convert date part to number

--  R e s u l t :
        ID ARRIVING  DEPARTURE
---------- --------- ---------
         1 01-APR-22 01-APR-22
         2 01-APR-22 01-APR-22
         3 01-APR-22 01-APR-22

How to compare two columns with only date not time

2 Answers2