Replacing specific date with a empty value in Oracle denodo

Question

I have a view where I am selecting a date column and reading it as a local date in denodo. In my column, I have a date '00-Jan-1900' like this which is giving issue so I wanted to replace this with empty. When I run this query, I get results as shown in the table2. But what I want is just an empty record in place of this date and looking like TABLE3.

Query:

select
to_localdate('dd-MM-yyyy', substring(replace("DATE", '00-Jan-1900', ''), 0, 10)) AS dtm
from XX

Table1:

DATE
22-Dec-2016
00-Jan-1900
30-Sep-2014

After replacing the date i get

Table2:

DATE
0201-12-22

0201-09-30

Is there a way i get output table like this?

TABLE3: DATE 22-Dec-2016

30-Sep-2014

Answer 1

In my column, I have a date '00-Jan-1900' like this which is giving issue so I wanted to replace this with empty.

In Oracle, you can use:

UPDATE table_name
SET    value = NULL
WHERE  EXTRACT( DAY FROM value ) = 0;

---

If you have the table:

CREATE TABLE table_name ( value DATE );

Then, some extra hoops need to be jumped through to insert an invalid date (namely generating the date from binary data so that the normal validation process for date values can be skipped) by creating this function:

CREATE FUNCTION createDate(
  year   int,
  month  int,
  day    int,
  hour   int,
  minute int,
  second int
) RETURN DATE DETERMINISTIC
IS
  hex CHAR(14);
  d DATE;
BEGIN
  hex := TO_CHAR( FLOOR( year / 100 ) + 100, 'fm0X' )
      || TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )
      || TO_CHAR( month, 'fm0X' )
      || TO_CHAR( day, 'fm0X' )
      || TO_CHAR( hour + 1, 'fm0X' )
      || TO_CHAR( minute + 1, 'fm0X' )
      || TO_CHAR( second + 1, 'fm0X' );
  DBMS_OUTPUT.PUT_LINE( hex );
  DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d );
  RETURN d;
END;
/

Then, you can have the data:

INSERT INTO table_name ( value )
SELECT DATE '1900-01-01' FROM DUAL UNION ALL
SELECT createDate( 1900, 1, 0, 0, 0, 0 ) FROM DUAL;

and:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT value,
       TO_CHAR( value, 'YYYY-MM-DD HH24:MI:SS' ) AS string_value
FROM   table_name;

Outputs:

VALUE               | STRING_VALUE       
:------------------ | :------------------
1900-01-01 00:00:00 | 1900-01-01 00:00:00
1900-01-00 00:00:00 | 0000-00-00 00:00:00

To get rid of your invalid value, you can use:

UPDATE table_name
SET    value = NULL
WHERE  EXTRACT( DAY FROM value ) = 0;

Then:

SELECT value,
       TO_CHAR( value, 'YYYY-MM-DD HH24:MI:SS' ) AS string_value
FROM   table_name;

Outputs:

VALUE               | STRING_VALUE       
:------------------ | :------------------
1900-01-01 00:00:00 | 1900-01-01 00:00:00
null                | null               

db<>fiddle here

---

Is there a way i get output table like this?

TABLE3:
DATE
22-Dec-2016

30-Sep-2014

You can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

And then just run your query and it should change the output (but you will need to make sure the format is set in future sessions when you run the query).

Or you can specify the format model using TO_CHAR:

SELECT TO_CHAR( your_column, 'DD-Mon-YYYY' ) AS your_column
FROM   your_table