英文:
How to compare two columns with only date not time
问题
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 three rows as my results, I want to compare just the date, not the time.
Can anyone in a simple query explain this?
英文:
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.
答案1
得分: 1
DATE 数据类型在 Oracle 中实际上是一个误称。它实际上是一个日期时间类型，包括日期部分和时间部分。要仅比较日期部分，请使用 TRUNC 将日期时间截断到午夜：
select *
from airport
where trunc(arriving) = trunc(departure);
英文:
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);
答案2
得分: 0
Oracle DATE由7个字节组成（世纪、年、月、日、小时、分钟、秒），要比较日期部分，需要去掉时间部分。有不同的方法可以实现。您可以选择适合您的任何一种方法。以下是包含三个不同Where子句的SQL选择命令的示例数据，它们都会得到相同的结果。
WITH -- 示例数据
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') -- 截断为日期
-- 或
Where To_Char(ARRIVING, 'dd.mm.yyyy') = To_Char(DEPARTURE, 'dd.mm.yyyy') -- 将日期部分转换为字符
-- 或
Where To_Number(To_Char(ARRIVING, 'yyyymmdd')) = To_Number(To_Char(DEPARTURE, 'yyyymmdd')) -- 将日期部分转换为数字
-- 结果：
ID ARRIVING DEPARTURE
---------- --------- ---------
1 01-APR-22 01-APR-22
2 01-APR-22 01-APR-22
3 01-APR-22 01-APR-22
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论