multiple - La comparación de fechas arroja resultados inusuales: SQL Oracle

pivot oracle ejemplos

Question

Tengo una tabla de la estructura:

+---------+--------------+-----------------+---------------+-------+------+ | week_no | long_week_no | week_start_date | week_end_date | month | year | +---------+--------------+-----------------+---------------+-------+------+ | 1 | 1A | 01/01/2015 | 03/01/2015 | JAN | 2015 | | 1 | 1B | 04/01/2015 | 10/01/2015 | JAN | 2015 | | 2 | 2 | 11/01/2015 | 17/01/2015 | JAN | 2015 | | 3 | 3 | 18/01/2015 | 24/01/2015 | JAN | 2015 | | .. | .. | .. | .. | .. | .. | | 51 | 51 | 14/12/2014 | 20/12/2015 | DEC | 2014 | +---------+--------------+-----------------+---------------+-------+------+

Cuando ejecuto la siguiente declaración:

SELECT * 
FROM   loy_period 
WHERE  To_date(''15/04/2015'', ''DD/MM/YYYY'') BETWEEN 
       To_date(week_start_date, ''DD/MM/YYYY'') AND 
       To_date(week_end_date, ''DD/MM/YYYY''); 

y

SELECT * 
FROM   loy_period 
WHERE  To_date(''15/04/2015'', ''DD/MM/YYYY'') BETWEEN 
       week_start_date AND 
       week_end_date; 

Devuelve lo siguiente:

+---------+--------------+-----------------+---------------+-------+------+ | week_no | long_week_no | week_start_date | week_end_date | month | year | +---------+--------------+-----------------+---------------+-------+------+ | 51 | 51 | 14/12/2014 | 20/12/2015 | DEC | 2014 | | 1 | 1A | 01/01/2015 | 03/01/2015 | JAN | 2015 | +---------+--------------+-----------------+---------------+-------+------+

¡Y cuando lo ejecuto con cualquier fecha, devolverá el período correcto además del registro week_no 51!

Estoy confundido por qué está pasando esto. Ambas columnas week_start_date y week_end_date de la date tipo.

Fiddle funciona como se esperaba.

Answer 1

Funciona perfectamente para mí con el uso adecuado de los valores TO_DATE y DATE.

• Nunca use TO_DATE en una FECHA , lo convertirá implícitamente en una cadena y luego volverá a estar actualizado utilizando el formato NLS específico de la configuración regional .

• ''01/01/2015'' NO es una FECHA, es un STRING. Debe usar TO_DATE para convertirlo explícitamente a DATE.

Mira qué pasa:

SQL> explain plan for select * from dual where to_date(sysdate) > to_date(sysdate -1);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(TO_DATE(TO_CHAR(SYSDATE@!))>TO_DATE(TO_CHAR(SYSDATE@!-1)))

14 rows selected.

SQL>

Entonces, el filtro real aplicado es filter (TO_DATE (TO_CHAR (SYSDATE @!)) Obtendrá resultados incorrectos debido a la conversión implícita basada en el formato NLS específico de la configuración regional.

De todos modos, ahora volviendo a su pregunta original.

Por ejemplo,

Digamos que su información se ve así:

Preparar:

SQL> CREATE TABLE t
  2      (week_no VARCHAR2(2), long_week_no VARCHAR2(2), week_start_date DATE, week_end_date DATE, mnth VARCHAR2(3), yr VARCHAR2(4))
  3  ;

Table created.

SQL>
SQL>
SQL> INSERT ALL
  2      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  3           VALUES (''1'', ''1A'', TO_DATE(''01/01/2015'',''DD/MM/YYYY''), TO_DATE(''03/01/2015'',''DD/MM/YYYY''), ''JAN'', ''2015'')
  4      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  5           VALUES (''1'', ''1B'', TO_DATE(''04/01/2015'',''DD/MM/YYYY''), TO_DATE(''10/01/2015'',''DD/MM/YYYY''), ''JAN'', ''2015'')
  6      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  7           VALUES (''2'', ''2'', TO_DATE(''11/01/2015'',''DD/MM/YYYY''), TO_DATE(''17/01/2015'',''DD/MM/YYYY''), ''JAN'', ''2015'')
  8      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  9           VALUES (''3'', ''3'', TO_DATE(''18/01/2015'',''DD/MM/YYYY''), TO_DATE(''24/01/2015'',''DD/MM/YYYY''), ''JAN'', ''2015'')
 10      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
 11           VALUES (''51'', ''51'', TO_DATE(''20/12/2014'',''DD/MM/YYYY''), TO_DATE(''26/12/2015'',''DD/MM/YYYY''), ''DEC'', ''2014'')
 12  SELECT * FROM dual
 13  ;

5 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>

Mesa:

SQL> SELECT * FROM t;

WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
1  1A 01-JAN-15 03-JAN-15 JAN 2015
1  1B 04-JAN-15 10-JAN-15 JAN 2015
2  2  11-JAN-15 17-JAN-15 JAN 2015
3  3  18-JAN-15 24-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014

SQL>

Consulta para filtrar las filas en función del rango DATE:

SQL> SELECT *
  2  FROM   t
  3  WHERE  To_date(''15/01/2015'', ''DD/MM/YYYY'') BETWEEN
  4         week_start_date AND
  5         week_end_date;

WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
2  2  11-JAN-15 17-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014

SQL>