1
    ALTER SESSION 
         SET NLS_DATE_FORMAT = 'DD-MM-YYYY';    
   
    SELECT 
         DEADLINE_DATE
        ,TO_CHAR(DEADLINE_DATE, 'mm/dd/yyyy hh24:mi:ss')
    FROM 
        SCHEMA.TABLE
    ORDER BY 
        DEADLINE_DATE asc

I'm hunting out invalid date values in my source, and running the above query finds me the following 4 records which all have out-of-range YYYY values.

enter image description here

Why though, do these 4 different DEADLINE_DATE values, when TO_CHAR is applied, convert to the same 00/00/0000 00:00:00 output, instead of e.g. '20/12/5048 00:00:0000', '24/10/5045 00:00:00', etc.?

Jon295087
  • 731
  • 1
  • 8
  • 28
  • 1
    Can you `dump()` those values? It looks like you do have invalid date values - [similar to this](https://stackoverflow.com/a/12456242/266304). – Alex Poole Nov 07 '22 at 16:25
  • and what is the data type of DEADLINE_DATE in the table? Please tell me it is not a string. – OldProgrammer Nov 07 '22 at 16:26
  • No, it is (at least) in DATA_TYPE = DATE format – Jon295087 Nov 07 '22 at 16:34
  • Updated the screenshot to include the dump() values, thanks @AlexPoole – Jon295087 Nov 07 '22 at 16:43
  • 1
    Its an invalid date. The `TO_CHAR` function has built-in error checking and will output zeroes when there is an invalid date. – MT0 Nov 07 '22 at 16:47
  • 3
    If your reverse engineer the first value you get `21/1/14` as ASCII code points, so this was obviously caused by some process that tried to pour a string directly into a `DATE` column as bytes. The second value is `24-5-07`, so unfortunately there wasn't even a consistent format, though with some luck it may be possible to reverse the operation for all values nonetheless (I don't have the Oracle knowledge for it). – Jeroen Mostert Nov 07 '22 at 16:49
  • 1
    You can get the same result if you plug your dump value (as hex, so 32312f312f3134 for the first one) into the code from from the answer I linked to before. [db<>fiddle](https://dbfiddle.uk/Lpv5A43M) seems to handle the NLS-dependant conversion differently, showing the year as -5051 instead of -5048 as SQL Developer shows; actually -5048-12-20 22:48:51. But it's essentially the same problem. Unless you have access to wherever these values came from, I doubt they're salvageable, sadly. – Alex Poole Nov 07 '22 at 16:55
  • 1
    @JeroenMostert You could use [fiddle](https://dbfiddle.uk/Vk7bwRQp) to extract the byte values and then convert it to characters and then back to a date. – MT0 Nov 07 '22 at 19:07

0 Answers0