日期类型上查询substr的索引是什么？

Question

嗨，我想做一个查询，以打印用户在ORACLE上的特定月份做一个动作SQL开发者。

以下是表格：

CREATE TABLE USUARIO2 (
    CODIGO NUMBER(10) CONSTRAINT USUARIO_PK PRIMARY KEY,
    NOMBRE VARCHAR(50) CONSTRAINT NOM_USUARIO_NN NOT NULL,
    TELEFONO VARCHAR2(12),
    DIRECCION VARCHAR2(100)
);

CREATE TABLE SACA (
    COD_USUARIO NUMBER(10),
    COD_EJEMPLAR NUMBER(10),
    FECHA_PRES DATE DEFAULT SYSDATE,
    FECHA_DEV DATE,
    CONSTRAINT SACA_PK PRIMARY KEY (COD_USUARIO, COD_EJEMPLAR, FECHA_PRES),
    CONSTRAINT USUARIO_FK FOREIGN KEY (COD_USUARIO) REFERENCES USUARIO2(CODIGO) ON DELETE CASCADE,
    CONSTRAINT EJEMPLAR_FK FOREIGN KEY (COD_EJEMPLAR) REFERENCES EJEMPLAR(CODIGO) ON DELETE CASCADE
);

以下是SACA的价值观：

INSERT INTO SACA VALUES (123,778899, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (123,112233, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (234,882143,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (234,777722,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (345,976543, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (456,446733,'04-01-2017','04-07-2017');
INSERT INTO SACA VALUES (567,778899, '01-01-2017','02-05-2017');
INSERT INTO SACA VALUES (890,222266,'04-01-2017','04-07-2017');

这就是查询。我想从FECHA\u DEV（代码）栏查询获得特定月份（05）书籍的用户。以下是查询：

SELECT usuario2.nombre
FROM usuario2
JOIN saca ON usuario2.codigo=saca.cod_usuario WHERE SUBSTR(FECHA_DEV,3,5) IN ('05');

查询为空，但如果更改了值，并从第一个和最后一个位置进行询问，则会正常工作。

那么，日期格式的月份索引是什么？

Answer 1

在SUBSTR中，最后一个参数是从第二个参数定义的位置开始的长度：

WHERE SUBSTR(FECHA_DEV, 3, 2) IN ('05'); -- changed 5 to 2

但是，您可以使用提取：

WHERE extract(month FROM FECHA_DEV) = 5

Answer 2

那么，日期格式的月份索引是什么？

这个问题的前提是有缺陷的，因为DATE是无格式的，因为它在内部存储为7个字节（分别为：世纪、世纪之年、月、月中日、小时、分钟和秒），所以没有DATE数据类型的通用字符串表示。

SUBSTR（string，start\u character，length）函数采用：

• 字符串作为其第一个参数

因此，第一个错误是，如果假设格式为DD-MM-RR，并且需要一个从字符串的第四个字符开始的2个字符长的子字符串，那么第二个和第三个参数应该是4，2，而不是3，5。

当您将DATE作为第一个参数传递时，Oracle将做的第一件事是尝试将DATE隐式转换为字符串，并且您的函数调用有效地：

SUBSTR(
  TO_CHAR(
    fecha_dev,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
  ),
  4,
  2
)

默认的NLS_DATE_FORMAT取决于NLS_TERRITORY设置，因此您不会在世界不同地区的用户之间获得一致的值（即在西班牙，默认为DD/MM/RR，而在美国是DD-MON-RR，月份显示为字符，在瑞典是RRRR-MM-DD，获取第4和第5个字符将给出最后一年的数字和连字符）。

即使您的用户都在同一个区域内，也不能保证他们不会设置自己的会话参数，因为任何用户都可以发出命令来更改他们的会话以进行更改。例如：

ALTER SESSION SET NLS_DATE_FORMAT = 'DD HH24MISS MONRR';

（是的，这种日期格式确实在某些设置中使用）

因此，您永远不应该依赖日期到字符串的隐式转换，因为您无法确定是否会获得一致的格式。

如果确实要使用SUBSTR，则应显式地将日期转换为字符串，并指定格式模型：

WHERE SUBSTR( TO_CHAR( fecha_dev, 'DD-MM-RR' ), 4, 2 ) IN ( '05' )

但是，如果要这样做，可以跳过SUBSTR，只输出月份：

WHERE TO_CHAR( fecha_dev, 'MM' ) = '05'

或者您可以使用提取：

WHERE EXTRACT( MONTH FROM fecha_dev ) = 5

Answer 3

我假设是Oracle，因为使用了number数据类型和sysdate引用。

不要对日期值使用字符串函数。它将在某一点或另一点失败（例如，在我的计算机上，SUBSTR（sysdate，3,5）返回-MAR-而不是03）。

最好将月份提取为数字。

因此，要获取5月份FECHA\u DEV所在的所有行，您应该使用：

SELECT usuario2.nombre
FROM usuario2
   JOIN saca ON usuario2.codigo=saca.cod_usuario 
WHERE extract(month from FECHA_DEV) = 5;

上述内容需要如下索引：

create index idx_fecha_dev_month on SACA  ( extract(month from FECHA_DEV)  );

是否使用该指数是另一个问题。您必须检查执行计划以进行验证。