Oracle Lag() on date output wrong data

All we need is an easy explanation of the problem, so here it is.

Oracle DB 19 – Sql developper 19 – NLS DD-MON-RR

I have a table

ID      NAME    EFFDT       NODE    PARENT
SHA     DEPT    01-JAN-01   A10200  TOP
SHA     DEPT    04-JAN-11   A10200  X10200
SHA     DEPT    01-JAN-14   A10200  TOP
SHA     DEPT    04-JAN-14   A10200  TOP

I’m using Lag() function in order to create time period

This is my query :

select id
       ,name
       ,effdt
       ,lag(effdt-1,1,null) over (partition by node order by effdt desc) AS EFFDTEND
       ,node
       ,parent
FROM
TABLE
ORDER BY node, effdt

Result :

ID      NAME    EFFDT       EFFDTEND    NODE    PARENT
SHA     DEPT    01-JAN-01   31-DEC-09   A10200  TOP
SHA     DEPT    04-JAN-11   31-DEC-12   A10200  X10200
SHA     DEPT    01-JAN-14   03-JAN-14   A10200  TOP
SHA     DEPT    04-JAN-14   05-JAN-14   A10200  TOP

Look at the first lines, i get 31-DEC-09 in place of 03-JAN-11 and 31-DEC-12 in place of 31-DEC-13

After 01-JAN-14 everthing works fine!

I already tried to :

  1. cast all effdt with TO_DATE() and NLS template
  2. use interval -1 day in place of effdt-1
SELECT 
TO_DATE('04-JAN-11', 'DD-MON-RR')-1
FROM DUAL;

Returns :

03-JAN-11

Update

As requested here is the Fiddle link : Fiddle

On Fiddle and 18c the error is not reproduced

select * from v$version;
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Any idea ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Looks OK on my 11gXE:

SQL> with test (id, name, effdt, node, parent) as
  2    (select 'SHA', 'DEPT', date '2001-01-01', 'A10200', 'TOP'    from dual union all
  3     select 'SHA', 'DEPT', date '2011-01-04', 'A10200', 'X10200' from dual union all
  4     select 'SHA', 'DEPT', date '2014-01-01', 'A10200', 'TOP'    from dual union all
  5     select 'SHA', 'DEPT', date '2014-01-04', 'A10200', 'TOP'    from dual
  6    )
  7  select id, name, effdt,
  8    lag(effdt - 1, 1, null) over (partition by node order by effdt desc) effdtend,
  9    node, parent
 10  from test
 11  order by node, effdt;

ID  NAME EFFDT     EFFDTEND  NODE   PARENT
--- ---- --------- --------- ------ ------
SHA DEPT 01-JAN-01 03-JAN-11 A10200 TOP
SHA DEPT 04-JAN-11 31-DEC-13 A10200 X10200
SHA DEPT 01-JAN-14 03-JAN-14 A10200 TOP
SHA DEPT 04-JAN-14           A10200 TOP

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

Method 2

I really don’t know why and how it happened but after a refresh by our ITOps of the standby logical DB, my problem is solved..

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply