CODEWITHSUNDEEP
oracle-database
NicoRiff
NicoRiff

Reputation: 4883

Cannot filter null Datetime values

I have a problem that is driving me crazy. I have to query an oracle view that returns some DATETIME values. The incredible problem is that even if I set the "IS NOT NULL" on the WHERE clause and even if I set the NVL(FECHA_HASTA, FECHA_DESDE), I´m still getting null values!!. How is that possible???

enter image description here

This is the query:

SELECT CUIL as Cuil, 
    COD_TIPO_CAUSAL as CodTipoCausal, 
    COD_CONVENIO as CodConvenio, 
    FECHA_DESDE as FechaDesde, 
    NVL(FECHA_HASTA, FECHA_DESDE) as FechaHasta
FROM ORGANISMO.VCAUSAL_AUSENCIA
WHERE FECHA_HASTA IS NOT NULL
AND FECHA_HASTA > (SELECT SYSDATE - 180 FROM SYS.DUAL)
AND CUIL IN (SELECT CUIL FROM ORGANISMO.VEMPLEADO WHERE FECHA_EGRESO IS NULL OR FECHA_EGRESO > (SELECT SYSDATE FROM SYS.DUAL))

EDIT:

Here is dump(fecha_hasta, 1016) added:

enter image description here

Upvotes: 0

Views: 947

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The dumped values show that the data is corrupt. The internal date format is well-known:

byte 1 - century (excess 100)  
byte 2 - year (excess 100)  
byte 3 - month  
byte 4 - day  
byte 5 - hour (excess 1)  
byte 6 - minute (excess 1)  
byte 7 - seconds (excess 1)

so the fourth byte in the two values that SQL Developer is reporting as null (even though they clearly are not actually null) should not be zero, as there is no day zero.

Based on those rules, 79,9d,2,0,18,3c,3c in hex, which is 121,157,2,0,24,60,60 in decimal, should convert as:

century: 121 - 100 = 21
year: 157 - 100 - 57
month: 2
day: 0
hour: 24 - 1 = 23
minute: 60 - 1 = 59
second: 60 - 1 = 59

or 2157-02-00 23:59:59. Similarly 78,b8,1,0,18,3c,3c converts to 2084-01-00 23:59:59.

Version 18.3 of SQL Developer displays those values, in both the script output and query results windows, as the previous day:

DT                  DUMPED                             
------------------- -----------------------------------
01-07-2020 23:59:59 Typ=12 Len=7: 78,78,7,1,18,3c,3c   
31-01-2157 23:59:59 Typ=12 Len=7: 79,9d,2,0,18,3c,3c   
31-12-2083 23:59:59 Typ=12 Len=7: 78,b8,1,0,18,3c,3c   
01-07-2018 00:00:00 Typ=12 Len=7: 78,76,7,1,1,1,1

whereas db<>fiddle shows the zero-day values.

So, since they are not actually null, it's reasonable that is not null and nvl() didn't affect them, and then it's up to the client or application as to how to present them.

The real issue is that you seem to have corrupted data in the tables underlying the view you're querying, so that needs to be investigated and fixed - assuming the invalid values can be safely identified, and you can find out what they should have been in the first place, which might be a struggle. Just filtering them out, either as part of the view or in your query, won't be simple though - unless you can filter out dates in the future. And assuming all the corruption is both that obvious and pushing dates into the future; on some level you have to question the validity of all of those dates... there could be much more subtle corruptions that look OK.

And then whatever process or tool caused the corruption needs to be tracked down and fixed so it doesn't happen again. Lots of things can cause corruption of course, but I believe imp used to have a bug that could corrupt dates and numbers, and OCI programs can too.

Upvotes: 2

Related Questions