甲骨文日期似乎相等 [英] Oracle date seems equals

问题描述

我的数据库中有一个表INCASSO:

there is a table INCASSO in my database:

CREATE TABLE "GEC_AP"."INCASSO" 
("ID_INCASSO" VARCHAR2(50 BYTE) NOT NULL ENABLE,
 "ID_FATTURA" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
 "ID_PIANO_RATE" VARCHAR2(22 BYTE) DEFAULT -1 NOT NULL ENABLE,
 "DATA_ESECUZIONE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
 ...)

主键包括四个字段:

CONSTRAINT "PK_INCASSO" PRIMARY KEY ("ID_INCASSO", "ID_FATTURA", "ID_PIANO_RATE", "DATA_ESECUZIONE")

当我运行以下查询时，似乎有重复的记录:

It seems there is a duplicated record when I run the following query:

select id_incasso, id_fattura, id_piano_rate, data_esecuzione
from incasso
where id_incasso = 'TO_20110521258225'

但是在另一个查询中，提取了0条记录:

But with another query, 0 records are extracted:

select id_incasso, id_fattura, id_piano_rate, data_esecuzione, count(*)
from incasso where id_incasso = 'TO_20110521258225'
group by id_incasso, id_fattura, id_piano_rate, data_esecuzione
having count(*) > 1

该数据库位于Oracle 11.2.0.1.0上，我正在使用SQL Developer 4.1.1.19.

The database is on Oracle 11.2.0.1.0 and I'm using SQL Developer 4.1.1.19.

在SQL Developer中，日期格式为:

In SQL Developer, the date format is:

我想知道记录是否不同或编辑器中是否存在格式化日期问题.如果记录基于日期不同，那么在日期的哪些部分不同?如果是编辑器中的格式化日期问题，该如何解决?

I would to know if the records are different or there is a format date problem in the editor. If the records are different based on the date, in which part of the date they are different? If it's a format date problem in the editor, how can I fix it?

推荐答案

将日期格式更改为DD-MON-YYYY HH24:MI:SS，您可能会看到不同之处，因为日期具有不同的世纪.

Change the date format to DD-MON-YYYY HH24:MI:SS and you are likely to see the difference in that the dates have different centuries.

使用RR格式化年份可以隐藏一个日期为1911，另一个日期为2011

Using RR to format the year can hide that one date is 1911 and the other is 2011

尝试:

SELECT TO_CHAR( DATE '2011-01-01', 'RR-MM-DD' ),
       TO_CHAR( DATE '1911-01-01', 'RR-MM-DD' )
FROM   DUAL

尽管它们是不同的日期，并且不会被分组在一起，但是两者将输出相同的结果.

Both will output the same although they are different dates and will not be grouped together.

如果日期仍然相同，则在字符串中寻找其他空格或其他隐藏字符；您可以使用LENGTH()检查字符串的大小，或使用DUMP()获取内容的字节值:

If the dates are still the same then look for additional spaces or other hidden characters in the strings; you can use LENGTH() to check the size of the strings or DUMP() to get the byte values of the contents:

select id_incasso,
       id_fattura,
       LENGTH( id_fattura ) AS f_length,
       id_piano_rate,
       LENGTH( id_piano_rate ) AS pr_length,
       TO_CHAR( data_esecuzione, 'YYYY-MM-DD HH24:MI:SS' ) AS data_esecuzione
from   incasso
where  id_incasso = 'TO_20110521258225'

这篇关于甲骨文日期似乎相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！